Обсуждение: pg_upgrade fails: Mismatch of relation OID in database 8.4 -> 9.3
Hi all, I'm trying to pg_upgrade an 8.4.21 to 9.3.4. The is on Debian 7--both versions were installed from apt.postgresql.org and are encoding "UTF8" and locale "C". Here's the error: /usr/lib/postgresql/9.3/bin/pg_upgrade \ -b /usr/lib/postgresql/8.4/bin/ \ -B /usr/lib/postgresql/9.3/bin/\ -d /var/lib/postgresql/8.4/main/ \ -D /var/lib/postgresql/9.3/main/ \ -p5433 \ -P5432 \ -u postgres \ -o "-c config_file=/etc/postgresql/8.4/main/postgresql.conf" \ -O "-c config_file=/etc/postgresql/9.3/main/postgresql.conf" Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking database user is a superuser ok Checking for prepared transactions ok Checking for reg* system OID user data types ok Checking for contrib/isn with bigint-passing mismatch ok Checking for large objects warning Your installation contains large objects. The new database has an additional large object permission table. After upgrading, you will be given a command to populate the pg_largeobject permission table with default permissions. Creating dump of global objects ok Creating dump of database schemas ok Checking for presence of required libraries ok Checking database user is a superuser ok Checking for prepared transactions ok If pg_upgrade fails after this point, you must re-initdb the new cluster before continuing. Performing Upgrade ------------------ Analyzing all rows in the new cluster ok Freezing all rows on the new cluster ok Deleting files from new pg_clog ok Copying old pg_clog to new server ok Setting next transaction ID for new cluster ok Setting oldest multixact ID on new cluster ok Resetting WAL archives ok Setting frozenxid counters in new cluster ok Restoring global objects in the new cluster ok Adding support functions to new cluster ok Restoring database schemas in the new cluster ok Removing support functions from new cluster ok Copying user relation files /var/lib/postgresql/8.4/main/base/4275487/4278965 Mismatch of relation OID in database "FNBooking": old OID 4279499, new OID 19792 Failure, exiting On 8.4.21, here's that OID: postgres=# \c "FNBooking" psql (9.3.4, server 8.4.21) You are now connected to database "FNBooking" as user "postgres". FNBooking=# SELECT relname, relfilenode, relkind from pg_class where oid = 4279499; relname | relfilenode | relkind ---------------+-------------+--------- abandone_conv | 4279499 | r (1 row) and on 9.3.4 it is the same: postgres@vdev1commandprompt2:~$ psql "FNBooking" psql (9.3.4) Type "help" for help. FNBooking=# SELECT relname, relfilenode, relkind from pg_class where oid = 4279499; relname | relfilenode | relkind ---------------+-------------+--------- abandone_conv | 4279499 | r (1 row) On 8.4.21, the new OID doesn't exist: FNBooking=# SELECT relname, relfilenode, relkind from pg_class where oid = 19792; relname | relfilenode | relkind ---------+-------------+--------- (0 rows) and on 9.3.4 it is this: FNBooking=# SELECT relname, relfilenode, relkind from pg_class where oid = 19792; relname | relfilenode | relkind ------------------+-------------+--------- pg_toast_4279527 | 19792 | t (1 row) Just to check, I did a pg_dump of the 8.4.21 FNBooking database and it restored with psql to 9.3.4 with no issues but the overall migration will really be too big to go this route. Any ideas? Thanks! Jeff Ross
On Tue, May 20, 2014 at 12:59:31PM -0600, Jeff Ross wrote: > Removing support functions from new cluster ok > Copying user relation files > /var/lib/postgresql/8.4/main/base/4275487/4278965 > Mismatch of relation OID in database "FNBooking": old OID 4279499, > new OID 19792 > Failure, exiting OK, those numbers are supposed to match. The array is ordered by OID and pg_upgrade expects a 1-to-1 mapping. > On 8.4.21, here's that OID: > > postgres=# \c "FNBooking" > psql (9.3.4, server 8.4.21) > You are now connected to database "FNBooking" as user "postgres". > FNBooking=# SELECT relname, relfilenode, relkind from pg_class where > oid = 4279499; > relname | relfilenode | relkind > ---------------+-------------+--------- > abandone_conv | 4279499 | r > (1 row) > > and on 9.3.4 it is the same: > > postgres@vdev1commandprompt2:~$ psql "FNBooking" > psql (9.3.4) > Type "help" for help. > > FNBooking=# SELECT relname, relfilenode, relkind from pg_class where > oid = 4279499; > relname | relfilenode | relkind > ---------------+-------------+--------- > abandone_conv | 4279499 | r > (1 row) Yes, they are supposed to match. > On 8.4.21, the new OID doesn't exist: > > FNBooking=# SELECT relname, relfilenode, relkind from pg_class where > oid = 19792; > relname | relfilenode | relkind > ---------+-------------+--------- > (0 rows) > > and on 9.3.4 it is this: > > FNBooking=# SELECT relname, relfilenode, relkind from pg_class where > oid = 19792; > relname | relfilenode | relkind > ------------------+-------------+--------- > pg_toast_4279527 | 19792 | t > (1 row) > > Just to check, I did a pg_dump of the 8.4.21 FNBooking database and > it restored with psql to 9.3.4 with no issues but the overall > migration will really be too big to go this route. So the problem is that some table in the new cluster got a low-numbered toast file and the version of the table in the old cluster probably doesn't have a toast file. Can you track down details on what table owns that toast file? Can you check on the table's layout to see what might have caused the toast table creation? Were columns added/removed? If you remove that table, does pg_upgrade then work? I am guessing it would. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
On 5/20/14, 2:22 PM, Bruce Momjian wrote: > > On Tue, May 20, 2014 at 12:59:31PM -0600, Jeff Ross wrote: >> Removing support functions from new cluster ok >> Copying user relation files >> /var/lib/postgresql/8.4/main/base/4275487/4278965 >> Mismatch of relation OID in database "FNBooking": old OID 4279499, >> new OID 19792 >> Failure, exiting > > OK, those numbers are supposed to match. The array is ordered by OID > and pg_upgrade expects a 1-to-1 mapping. Ah, so I misunderstood the error message--thanks for clearing that up. > >> On 8.4.21, here's that OID: >> >> postgres=# \c "FNBooking" >> psql (9.3.4, server 8.4.21) >> You are now connected to database "FNBooking" as user "postgres". >> FNBooking=# SELECT relname, relfilenode, relkind from pg_class where >> oid = 4279499; >> relname | relfilenode | relkind >> ---------------+-------------+--------- >> abandone_conv | 4279499 | r >> (1 row) >> >> and on 9.3.4 it is the same: >> >> postgres@vdev1commandprompt2:~$ psql "FNBooking" >> psql (9.3.4) >> Type "help" for help. >> >> FNBooking=# SELECT relname, relfilenode, relkind from pg_class where >> oid = 4279499; >> relname | relfilenode | relkind >> ---------------+-------------+--------- >> abandone_conv | 4279499 | r >> (1 row) > > Yes, they are supposed to match. > >> On 8.4.21, the new OID doesn't exist: >> >> FNBooking=# SELECT relname, relfilenode, relkind from pg_class where >> oid = 19792; >> relname | relfilenode | relkind >> ---------+-------------+--------- >> (0 rows) >> >> and on 9.3.4 it is this: >> >> FNBooking=# SELECT relname, relfilenode, relkind from pg_class where >> oid = 19792; >> relname | relfilenode | relkind >> ------------------+-------------+--------- >> pg_toast_4279527 | 19792 | t >> (1 row) >> >> Just to check, I did a pg_dump of the 8.4.21 FNBooking database and >> it restored with psql to 9.3.4 with no issues but the overall >> migration will really be too big to go this route. > > So the problem is that some table in the new cluster got a low-numbered > toast file and the version of the table in the old cluster probably > doesn't have a toast file. > > Can you track down details on what table owns that toast file? Can you > check on the table's layout to see what might have caused the toast > table creation? Were columns added/removed? If you remove that table, > does pg_upgrade then work? I am guessing it would. > Here's a sample from a different database that failed with the same problem. Error: Mismatch of relation OID in database "UDB": old OID 1163225, new OID 22588 postgres@vdev1commandprompt2:~$ psql "UDB" psql (9.3.4) Type "help" for help. UDB=# \x Expanded display is on. UDB=# select * from pg_class where reltoastrelid = 22588; -[ RECORD 1 ]--+-------------------------------------------------------------------------------------------------- relname | contact_email relnamespace | 2200 reltype | 17262 reloftype | 0 relowner | 10 relam | 0 relfilenode | 17260 reltablespace | 0 relpages | 0 reltuples | 0 relallvisible | 0 reltoastrelid | 22588 reltoastidxid | 0 relhasindex | t relisshared | f relpersistence | p relkind | r relnatts | 5 relchecks | 0 relhasoids | f relhaspkey | t relhasrules | f relhastriggers | t relhassubclass | f relispopulated | t relfrozenxid | 1944 relminmxid | 2 relacl | {postgres=arwdDxt/postgres,fnv2=arwd/postgres,webv2=arwd/postgres,krish=r/postgres,fm=r/postgres} reloptions | UDB=# \d+ contact_email Table "public.contact_email" Column | Type | Modifiers | Storage | Stats target | Description --------+-----------------------------+------------------------------------------------------------+----------+--------------+------------- id | integer | not null default nextval('contact_email_id_seq'::regclass) | plain | | email1 | character varying(255) | not null | extended | | email2 | character varying(255) | |extended | | time | timestamp without time zone | not null default now() | plain | | source | email_source | not null | plain | | Indexes: "contact_email_pkey" PRIMARY KEY, btree (id) "idx_contact_email_email1" btree (lower(email1::text) varchar_pattern_ops) "idx_contact_email_email2" btree (lower(email2::text) varchar_pattern_ops) Referenced by: TABLE "abandoned_master_booking" CONSTRAINT "abandoned_master_booking_contact_email_id_fkey" FOREIGN KEY (contact_email_id) REFERENCES contact_email(id) TABLE "contact" CONSTRAINT "contact_contact_email_id_fkey" FOREIGN KEY (contact_email_id) REFERENCES contact_email(id) TABLE "eticketaccesslog" CONSTRAINT "eticketaccesslog_contact_email_id_fkey" FOREIGN KEY (contact_email_id) REFERENCES contact_email(id) Has OIDs: no UDB=# \dT+ email_source List of data types -[ RECORD 1 ]-----+------------- Schema | public Name | email_source Internal name | email_source Size | 4 Elements | Booking | User Profile | UserProfile Access privileges | =U/postgres Description | I do not know if columns were added or removed. Dropping the table from the last database that caused pg_upgrade to fail let pg_upgrade proceed on through many more before it failed again on the UDB database, so that's progress! If there is anything else I can provide, let me know. Thanks, Bruce! Jeff
On Tue, May 20, 2014 at 03:25:00PM -0600, Jeff Ross wrote: > Here's a sample from a different database that failed with the same problem. > > Error: Mismatch of relation OID in database "UDB": old OID 1163225, > new OID 22588 > postgres@vdev1commandprompt2:~$ psql "UDB" > psql (9.3.4) > Type "help" for help. > > UDB=# \x > Expanded display is on. > UDB=# select * from pg_class where reltoastrelid = 22588; > -[ RECORD 1 ]--+-------------------------------------------------------------------------------------------------- > relname | contact_email > > UDB=# \dT+ email_source > List of data types > -[ RECORD 1 ]-----+------------- > Schema | public > Name | email_source > Internal name | email_source > Size | 4 > Elements | Booking > | User Profile > | UserProfile > Access privileges | =U/postgres > Description | > > I do not know if columns were added or removed. > > Dropping the table from the last database that caused pg_upgrade to > fail let pg_upgrade proceed on through many more before it failed > again on the UDB database, so that's progress! > > If there is anything else I can provide, let me know. OK, so we have code in the backend to force a toast table in the new cluster if there isn't one in the old cluster, e.g.: /* * Check to see whether the table actually needs a TOAST table. * * If an update-in-place toast relfilenodeis specified, force toast file * creation even if it seems not to need one. */ if (!needs_toast_table(rel)&& (!IsBinaryUpgrade || !OidIsValid(binary_upgrade_next_toast_pg_class_oid))) return false; What we never considered is case where the old cluster didn't have one and the new one does. What I would like you to do is to use the 9.3 pg_dump and create a schema-only dump: pg_dump --schema-only --binary-upgrade Find the table that is getting the toast file on the new cluster but not the old one, and run all the commands related to that table from the dump --- you don't need to load any data, just the schema items. Run that on the old cluster and the new cluster, then check if you are getting the same case where there is no toast table on the old cluster but one on the new cluster. Remember to only use the 9.3 pg_dump output for both old and new clusters. Thanks. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
On Tue, May 20, 2014 at 10:25:44PM -0400, Bruce Momjian wrote: > Find the table that is getting the toast file on the new cluster but not > the old one, and run all the commands related to that table from the > dump --- you don't need to load any data, just the schema items. > > Run that on the old cluster and the new cluster, then check if you are > getting the same case where there is no toast table on the old cluster > but one on the new cluster. Remember to only use the 9.3 pg_dump output > for both old and new clusters. FYI, you might want to test this by using a different database in the old cluster so you don't interfere with the existing data. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
On 5/21/14, 9:13 AM, Bruce Momjian wrote: > > On Wed, May 21, 2014 at 09:11:05AM -0600, Jeff Ross wrote: >> -- For binary upgrade, set heap's relfrozenxid >> UPDATE pg_catalog.pg_class >> SET relfrozenxid = '1944' >> WHERE oid = 'contact_email'::pg_catalog.regclass; >> >> -- For binary upgrade, set toast's relfrozenxid >> UPDATE pg_catalog.pg_class >> SET relfrozenxid = '2715452' >> WHERE oid = '22588'; >> >> >> I should run the update commands only, correct? > > Uh, not really. We need to replicate what pg_upgrade is doing, and I > can't be sure I am replicating your environment if I try it here. > > You need to use 9.3 pg_dump to dump the schema on 8.4, then use that > dump to recreate the table on 8.4 _and_ 9.3 and then check for toast > existance. You are not going to do a pg_dump on 9.3. > Thanks for the clarification. Yes, using 9.3's pg_dump of 8.4 and applying it to both an 8.4 and a 9.3 cluster, the contact_email table now has toast in both 8.4 and 9.3. Jeff
On Wed, May 21, 2014 at 10:56:59AM -0600, Jeff Ross wrote: > > On 5/21/14, 9:13 AM, Bruce Momjian wrote: > > > >On Wed, May 21, 2014 at 09:11:05AM -0600, Jeff Ross wrote: > >>-- For binary upgrade, set heap's relfrozenxid > >>UPDATE pg_catalog.pg_class > >>SET relfrozenxid = '1944' > >>WHERE oid = 'contact_email'::pg_catalog.regclass; > >> > >>-- For binary upgrade, set toast's relfrozenxid > >>UPDATE pg_catalog.pg_class > >>SET relfrozenxid = '2715452' > >>WHERE oid = '22588'; > >> > >> > >>I should run the update commands only, correct? > > > >Uh, not really. We need to replicate what pg_upgrade is doing, and I > >can't be sure I am replicating your environment if I try it here. > > > >You need to use 9.3 pg_dump to dump the schema on 8.4, then use that > >dump to recreate the table on 8.4 _and_ 9.3 and then check for toast > >existance. You are not going to do a pg_dump on 9.3. > > > > > Thanks for the clarification. > > Yes, using 9.3's pg_dump of 8.4 and applying it to both an 8.4 and a > 9.3 cluster, the contact_email table now has toast in both 8.4 and > 9.3. So the big question is why doesn't the existing 8.4 table have a toast table? Did you use pg_upgrade on the old cluster before, e.g. from 8.3? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Bruce Momjian <bruce@momjian.us> writes: > On Wed, May 21, 2014 at 10:56:59AM -0600, Jeff Ross wrote: >> Yes, using 9.3's pg_dump of 8.4 and applying it to both an 8.4 and a >> 9.3 cluster, the contact_email table now has toast in both 8.4 and >> 9.3. > So the big question is why doesn't the existing 8.4 table have a toast > table? Did you use pg_upgrade on the old cluster before, e.g. from 8.3? I have a vague recollection that we changed the calculations about whether a toast table would be needed, but I can't find it in the commit logs right now --- and anyway I think this was pre-8.4. regards, tom lane
On Wed, May 21, 2014 at 04:23:34PM -0400, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > On Wed, May 21, 2014 at 10:56:59AM -0600, Jeff Ross wrote: > >> Yes, using 9.3's pg_dump of 8.4 and applying it to both an 8.4 and a > >> 9.3 cluster, the contact_email table now has toast in both 8.4 and > >> 9.3. > > > So the big question is why doesn't the existing 8.4 table have a toast > > table? Did you use pg_upgrade on the old cluster before, e.g. from 8.3? > > I have a vague recollection that we changed the calculations about whether > a toast table would be needed, but I can't find it in the commit logs > right now --- and anyway I think this was pre-8.4. The only item I can think of that would cause this is someone changing the length of a string. Did someone modify pg_attribute directly to increase the length of one of the character columns? I just tested ALTER TABLE in 8.4 and it does create a toast table for this case in 9.4: CREATE TABLE test (x CHAR(10));ALTER TABLE test ALTER COLUMN x TYPE CHAR(8000); -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
On Wed, May 21, 2014 at 04:37:13PM -0400, Bruce Momjian wrote: > On Wed, May 21, 2014 at 04:23:34PM -0400, Tom Lane wrote: > > Bruce Momjian <bruce@momjian.us> writes: > > > On Wed, May 21, 2014 at 10:56:59AM -0600, Jeff Ross wrote: > > >> Yes, using 9.3's pg_dump of 8.4 and applying it to both an 8.4 and a > > >> 9.3 cluster, the contact_email table now has toast in both 8.4 and > > >> 9.3. > > > > > So the big question is why doesn't the existing 8.4 table have a toast > > > table? Did you use pg_upgrade on the old cluster before, e.g. from 8.3? > > > > I have a vague recollection that we changed the calculations about whether > > a toast table would be needed, but I can't find it in the commit logs > > right now --- and anyway I think this was pre-8.4. > > The only item I can think of that would cause this is someone changing > the length of a string. Did someone modify pg_attribute directly to > increase the length of one of the character columns? > > I just tested ALTER TABLE in 8.4 and it does create a toast table for > this case in 9.4: > > CREATE TABLE test (x CHAR(10)); > ALTER TABLE test ALTER COLUMN x TYPE CHAR(8000); Has anyone changed the database encoding by modifying the system catalogs? That might cause the problem too. Moving forward, I think you need to add a dummy column to each problem table and drop the column ---- that will create a toast table and allow you to do the upgrade. I could have pg_upgrade detect this problem, but until I know the cause, I don't think that is wise. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Bruce Momjian wrote: > Moving forward, I think you need to add a dummy column to each problem > table and drop the column ---- that will create a toast table and allow > you to do the upgrade. I could have pg_upgrade detect this problem, but > until I know the cause, I don't think that is wise. Maybe --check mode could examine both clusters and see whether each table having toast table or not matches. That wouldn't solve the actual problem but at least give a clue, instead of these very obscure problems. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Alvaro Herrera <alvherre@2ndquadrant.com> writes: > Bruce Momjian wrote: >> Moving forward, I think you need to add a dummy column to each problem >> table and drop the column ---- that will create a toast table and allow >> you to do the upgrade. I could have pg_upgrade detect this problem, but >> until I know the cause, I don't think that is wise. > Maybe --check mode could examine both clusters and see whether each > table having toast table or not matches. That wouldn't solve the actual > problem but at least give a clue, instead of these very obscure > problems. Well, the case of not needing a toast table anymore is expected (drop a wide column). What we're scratching our heads over is the other case. regards, tom lane
On 5/21/14, 2:37 PM, Bruce Momjian wrote: > > On Wed, May 21, 2014 at 04:23:34PM -0400, Tom Lane wrote: >> Bruce Momjian <bruce@momjian.us> writes: >>> On Wed, May 21, 2014 at 10:56:59AM -0600, Jeff Ross wrote: >>>> Yes, using 9.3's pg_dump of 8.4 and applying it to both an 8.4 and a >>>> 9.3 cluster, the contact_email table now has toast in both 8.4 and >>>> 9.3. >> >>> So the big question is why doesn't the existing 8.4 table have a toast >>> table? Did you use pg_upgrade on the old cluster before, e.g. from 8.3? >> >> I have a vague recollection that we changed the calculations about whether >> a toast table would be needed, but I can't find it in the commit logs >> right now --- and anyway I think this was pre-8.4. > > The only item I can think of that would cause this is someone changing > the length of a string. Did someone modify pg_attribute directly to > increase the length of one of the character columns? > I don't know, sorry. > I just tested ALTER TABLE in 8.4 and it does create a toast table for > this case in 9.4: > > CREATE TABLE test (x CHAR(10)); > ALTER TABLE test ALTER COLUMN x TYPE CHAR(8000); > I just tried this on the problem table and it did indeed create a toast table. I then retried pg_upgrade and it failed with the same problem on a different table in the same database. Of the 67 databases in the 8.4 cluster, 5 (so far) have had this problem on at least one table. Jeff
On Thu, May 22, 2014 at 09:20:38AM -0600, Jeff Ross wrote: > >I just tested ALTER TABLE in 8.4 and it does create a toast table for > >this case in 9.4: > > > > CREATE TABLE test (x CHAR(10)); > > ALTER TABLE test ALTER COLUMN x TYPE CHAR(8000); > > > I just tried this on the problem table and it did indeed create a > toast table. > > I then retried pg_upgrade and it failed with the same problem on a > different table in the same database. Of the 67 databases in the > 8.4 cluster, 5 (so far) have had this problem on at least one table. Yeah, it would be nice to be able to report all the problem tables, but I don't know how to do that except from pg_upgrade failing. Is there anything similar about these tables? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Bruce Momjian wrote > On Thu, May 22, 2014 at 09:20:38AM -0600, Jeff Ross wrote: >> >I just tested ALTER TABLE in 8.4 and it does create a toast table for >> >this case in 9.4: >> > >> > CREATE TABLE test (x CHAR(10)); >> > ALTER TABLE test ALTER COLUMN x TYPE CHAR(8000); >> > >> I just tried this on the problem table and it did indeed create a >> toast table. >> >> I then retried pg_upgrade and it failed with the same problem on a >> different table in the same database. Of the 67 databases in the >> 8.4 cluster, 5 (so far) have had this problem on at least one table. > > Yeah, it would be nice to be able to report all the problem tables, but > I don't know how to do that except from pg_upgrade failing. Is there > anything similar about these tables? Would a toast table in this situation have to be empty on the 8.4 database? Is there some kind of stat table query that would identify all such toast tables? Although it is possible some of those tables do indeed need a toast table but never make use of it (especially if one makes judicious use of unlimited text columns but never fills them with large amounts of data - like for lookup tables). David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-upgrade-fails-Mismatch-of-relation-OID-in-database-8-4-9-3-tp5804593p5804793.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On 2014-05-22 09:20:38 -0600, Jeff Ross wrote: > On 5/21/14, 2:37 PM, Bruce Momjian wrote: > >The only item I can think of that would cause this is someone changing > >the length of a string. Did someone modify pg_attribute directly to > >increase the length of one of the character columns? > I don't know, sorry. > >I just tested ALTER TABLE in 8.4 and it does create a toast table for > >this case in 9.4: > > > > CREATE TABLE test (x CHAR(10)); > > ALTER TABLE test ALTER COLUMN x TYPE CHAR(8000); > > > I just tried this on the problem table and it did indeed create a toast > table. > > I then retried pg_upgrade and it failed with the same problem on a different > table in the same database. Of the 67 databases in the 8.4 cluster, 5 (so > far) have had this problem on at least one table. Any chance you could, *before* you create the toast table, do a: SELECT attrelid::regclass, attname, attnum, attlen, * FROM pg_attribute WHERE attrelid = 'a'::regclass ORDER BY attnum ASC; Where 'a' is replaced by the affected table? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Thu, May 22, 2014 at 09:55:10AM -0400, Alvaro Herrera wrote: > Bruce Momjian wrote: > > > Moving forward, I think you need to add a dummy column to each problem > > table and drop the column ---- that will create a toast table and allow > > you to do the upgrade. I could have pg_upgrade detect this problem, but > > until I know the cause, I don't think that is wise. > > Maybe --check mode could examine both clusters and see whether each > table having toast table or not matches. That wouldn't solve the actual > problem but at least give a clue, instead of these very obscure > problems. There is no way to check for an old/new toast mismatch except creating the tables on the new cluster, and check mode can't do that due to time and because it would modify the new cluster and make it non-upgradeable. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
On 5/23/14, 7:36 AM, Andres Freund wrote: > > Any chance you could, *before* you create the toast table, do a: > SELECT attrelid::regclass, attname, attnum, attlen, * > FROM pg_attribute > WHERE attrelid = 'a'::regclass > ORDER BY attnum ASC; > > Where 'a' is replaced by the affected table? > > Greetings, > > Andres Freund > Yes, here you go: postgres@vdev1commandprompt2:~$ psql -p 5433 UDB psql (9.3.4, server 8.4.21) Type "help" for help. UDB=# \x Expanded display is on. UDB=# SELECT attrelid::regclass, attname, attnum, attlen, * FROM pg_attribute WHERE attrelid = 'masterairportlist'::regclass ORDER BY attnum ASC; UDB=# [ RECORD 1 ]-+------------------ attrelid | masterairportlist attname | tableoid attnum | -7 attlen | 4 attrelid | 18098 attname | tableoid atttypid | 26 attstattarget | 0 attlen | 4 attnum | -7 attndims | 0 attcacheoff | -1 atttypmod | -1 attbyval | t attstorage | p attalign | i attnotnull | t atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 2 ]-+------------------ attrelid | masterairportlist attname | cmax attnum | -6 attlen | 4 attrelid | 18098 attname | cmax atttypid | 29 attstattarget | 0 attlen | 4 attnum | -6 attndims | 0 attcacheoff | -1 atttypmod | -1 attbyval | t attstorage | p attalign | i attnotnull | t atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 3 ]-+------------------ attrelid | masterairportlist attname | xmax attnum | -5 attlen | 4 attrelid | 18098 attname | xmax atttypid | 28 attstattarget | 0 attlen | 4 attnum | -5 attndims | 0 attcacheoff | -1 atttypmod | -1 attbyval | t attstorage | p attalign | i attnotnull | t atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 4 ]-+------------------ attrelid | masterairportlist attname | cmin attnum | -4 attlen | 4 attrelid | 18098 attname | cmin atttypid | 29 attstattarget | 0 attlen | 4 attnum | -4 attndims | 0 attcacheoff | -1 atttypmod | -1 attbyval | t attstorage | p attalign | i attnotnull | t atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 5 ]-+------------------ attrelid | masterairportlist attname | xmin attnum | -3 attlen | 4 attrelid | 18098 attname | xmin atttypid | 28 attstattarget | 0 attlen | 4 attnum | -3 attndims | 0 attcacheoff | -1 atttypmod | -1 attbyval | t attstorage | p attalign | i attnotnull | t atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 6 ]-+------------------ attrelid | masterairportlist attname | ctid attnum | -1 attlen | 6 attrelid | 18098 attname | ctid atttypid | 27 attstattarget | 0 attlen | 6 attnum | -1 attndims | 0 attcacheoff | -1 atttypmod | -1 attbyval | f attstorage | p attalign | s attnotnull | t atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 7 ]-+------------------ attrelid | masterairportlist attname | id attnum | 1 attlen | 4 attrelid | 18098 attname | id atttypid | 23 attstattarget | -1 attlen | 4 attnum | 1 attndims | 0 attcacheoff | -1 atttypmod | -1 attbyval | t attstorage | p attalign | i attnotnull | t atthasdef | t attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 8 ]-+------------------ attrelid | masterairportlist attname | airport_code attnum | 2 attlen | -1 attrelid | 18098 attname | airport_code atttypid | 1043 attstattarget | -1 attlen | -1 attnum | 2 attndims | 0 attcacheoff | -1 atttypmod | 7 attbyval | f attstorage | x attalign | i attnotnull | f atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 9 ]-+------------------ attrelid | masterairportlist attname | airport_name attnum | 3 attlen | -1 attrelid | 18098 attname | airport_name atttypid | 1043 attstattarget | -1 attlen | -1 attnum | 3 attndims | 0 attcacheoff | -1 atttypmod | 68 attbyval | f attstorage | x attalign | i attnotnull | f atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 10 ]+------------------ attrelid | masterairportlist attname | city_name attnum | 4 attlen | -1 attrelid | 18098 attname | city_name atttypid | 1043 attstattarget | -1 attlen | -1 attnum | 4 attndims | 0 attcacheoff | -1 atttypmod | 68 attbyval | f attstorage | x attalign | i attnotnull | f atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 11 ]+------------------ attrelid | masterairportlist attname | confirm attnum | 5 attlen | 1 attrelid | 18098 attname | confirm atttypid | 16 attstattarget | -1 attlen | 1 attnum | 5 attndims | 0 attcacheoff | -1 atttypmod | -1 attbyval | t attstorage | p attalign | c attnotnull | f atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 12 ]+------------------ attrelid | masterairportlist attname | country_code attnum | 6 attlen | -1 attrelid | 18098 attname | country_code atttypid | 1043 attstattarget | -1 attlen | -1 attnum | 6 attndims | 0 attcacheoff | -1 atttypmod | 6 attbyval | f attstorage | x attalign | i attnotnull | f atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 13 ]+------------------ attrelid | masterairportlist attname | country_name attnum | 7 attlen | -1 attrelid | 18098 attname | country_name atttypid | 1043 attstattarget | -1 attlen | -1 attnum | 7 attndims | 0 attcacheoff | -1 atttypmod | 68 attbyval | f attstorage | x attalign | i attnotnull | f atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 14 ]+------------------ attrelid | masterairportlist attname | destrank attnum | 8 attlen | 4 attrelid | 18098 attname | destrank atttypid | 23 attstattarget | -1 attlen | 4 attnum | 8 attndims | 0 attcacheoff | -1 atttypmod | -1 attbyval | t attstorage | p attalign | i attnotnull | f atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 15 ]+------------------ attrelid | masterairportlist attname | dstflag attnum | 9 attlen | 1 attrelid | 18098 attname | dstflag atttypid | 16 attstattarget | -1 attlen | 1 attnum | 9 attndims | 0 attcacheoff | -1 atttypmod | -1 attbyval | t attstorage | p attalign | c attnotnull | f atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 16 ]+------------------ attrelid | masterairportlist attname | enddst attnum | 10 attlen | 8 attrelid | 18098 attname | enddst atttypid | 1114 attstattarget | -1 attlen | 8 attnum | 10 attndims | 0 attcacheoff | -1 atttypmod | -1 attbyval | t attstorage | p attalign | d attnotnull | f atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 17 ]+------------------ attrelid | masterairportlist attname | fr_airport_name attnum | 11 attlen | -1 attrelid | 18098 attname | fr_airport_name atttypid | 1043 attstattarget | -1 attlen | -1 attnum | 11 attndims | 0 attcacheoff | -1 atttypmod | 260 attbyval | f attstorage | x attalign | i attnotnull | f atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 18 ]+------------------ attrelid | masterairportlist attname | fr_city_name attnum | 12 attlen | -1 attrelid | 18098 attname | fr_city_name atttypid | 1043 attstattarget | -1 attlen | -1 attnum | 12 attndims | 0 attcacheoff | -1 atttypmod | 68 attbyval | f attstorage | x attalign | i attnotnull | f atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 19 ]+------------------ attrelid | masterairportlist attname | fr_country_code attnum | 13 attlen | -1 attrelid | 18098 attname | fr_country_code atttypid | 1043 attstattarget | -1 attlen | -1 attnum | 13 attndims | 0 attcacheoff | -1 atttypmod | 6 attbyval | f attstorage | x attalign | i attnotnull | f atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 20 ]+------------------ attrelid | masterairportlist attname | fr_country_name attnum | 14 attlen | -1 attrelid | 18098 attname | fr_country_name atttypid | 1043 attstattarget | -1 attlen | -1 attnum | 14 attndims | 0 attcacheoff | -1 atttypmod | 68 attbyval | f attstorage | x attalign | i attnotnull | f atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 21 ]+------------------ attrelid | masterairportlist attname | fr_prov_state attnum | 15 attlen | -1 attrelid | 18098 attname | fr_prov_state atttypid | 1043 attstattarget | -1 attlen | -1 attnum | 15 attndims | 0 attcacheoff | -1 atttypmod | 68 attbyval | f attstorage | x attalign | i attnotnull | f atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 22 ]+------------------ attrelid | masterairportlist attname | origrank attnum | 16 attlen | 4 attrelid | 18098 attname | origrank atttypid | 23 attstattarget | -1 attlen | 4 attnum | 16 attndims | 0 attcacheoff | -1 atttypmod | -1 attbyval | t attstorage | p attalign | i attnotnull | f atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 23 ]+------------------ attrelid | masterairportlist attname | prov_code attnum | 17 attlen | -1 attrelid | 18098 attname | prov_code atttypid | 1043 attstattarget | -1 attlen | -1 attnum | 17 attndims | 0 attcacheoff | -1 atttypmod | 104 attbyval | f attstorage | x attalign | i attnotnull | f atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 24 ]+------------------ attrelid | masterairportlist attname | prov_name attnum | 18 attlen | -1 attrelid | 18098 attname | prov_name atttypid | 1043 attstattarget | -1 attlen | -1 attnum | 18 attndims | 0 attcacheoff | -1 atttypmod | 104 attbyval | f attstorage | x attalign | i attnotnull | f atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 25 ]+------------------ attrelid | masterairportlist attname | prov_state attnum | 19 attlen | -1 attrelid | 18098 attname | prov_state atttypid | 1043 attstattarget | -1 attlen | -1 attnum | 19 attndims | 0 attcacheoff | -1 atttypmod | 68 attbyval | f attstorage | x attalign | i attnotnull | f atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 26 ]+------------------ attrelid | masterairportlist attname | startdst attnum | 20 attlen | 8 attrelid | 18098 attname | startdst atttypid | 1114 attstattarget | -1 attlen | 8 attnum | 20 attndims | 0 attcacheoff | -1 atttypmod | -1 attbyval | t attstorage | p attalign | d attnotnull | f atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 27 ]+------------------ attrelid | masterairportlist attname | timeoffset attnum | 21 attlen | -1 attrelid | 18098 attname | timeoffset atttypid | 1043 attstattarget | -1 attlen | -1 attnum | 21 attndims | 0 attcacheoff | -1 atttypmod | 104 attbyval | f attstorage | x attalign | i attnotnull | f atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl | -[ RECORD 28 ]+------------------ attrelid | masterairportlist attname | timeref attnum | 22 attlen | -1 attrelid | 18098 attname | timeref atttypid | 1043 attstattarget | -1 attlen | -1 attnum | 22 attndims | 0 attcacheoff | -1 atttypmod | 104 attbyval | f attstorage | x attalign | i attnotnull | f atthasdef | f attisdropped | f attislocal | t attinhcount | 0 attacl |
On 5/23/14, 7:21 AM, Bruce Momjian wrote: > > On Thu, May 22, 2014 at 09:20:38AM -0600, Jeff Ross wrote: >>> I just tested ALTER TABLE in 8.4 and it does create a toast table for >>> this case in 9.4: >>> >>> CREATE TABLE test (x CHAR(10)); >>> ALTER TABLE test ALTER COLUMN x TYPE CHAR(8000); >>> >> I just tried this on the problem table and it did indeed create a >> toast table. >> >> I then retried pg_upgrade and it failed with the same problem on a >> different table in the same database. Of the 67 databases in the >> 8.4 cluster, 5 (so far) have had this problem on at least one table. > > Yeah, it would be nice to be able to report all the problem tables, but > I don't know how to do that except from pg_upgrade failing. Is there > anything similar about these tables? > Here are the last 2 tables I had a problem with: UDB=# \d contact_email Table "public.contact_email" Column | Type | Modifiers --------+-----------------------------+------------------------------------------------------------ id | integer | not null default nextval('contact_email_id_seq'::regclass) email1 | character varying(255) | not null email2 | character varying(255) | time | timestamp without time zone | not null default now() source | email_source | notnull Indexes: "contact_email_pkey" PRIMARY KEY, btree (id) "idx_contact_email_email1" btree (lower(email1::text) varchar_pattern_ops) "idx_contact_email_email2" btree (lower(email2::text) varchar_pattern_ops) Referenced by: TABLE "abandoned_master_booking" CONSTRAINT "abandoned_master_booking_contact_email_id_fkey" FOREIGN KEY (contact_email_id) REFERENCES contact_email(id) TABLE "contact" CONSTRAINT "contact_contact_email_id_fkey" FOREIGN KEY (contact_email_id) REFERENCES contact_email(id) TABLE "eticketaccesslog" CONSTRAINT "eticketaccesslog_contact_email_id_fkey" FOREIGN KEY (contact_email_id) REFERENCES contact_email(id) UDB=# \d masterairportlist Table "public.masterairportlist" Column | Type | Modifiers -----------------+-----------------------------+---------------------------------------------------------------- id | integer | not null default nextval('masterairportlist_id_seq'::regclass) airport_code | character varying(3) | airport_name | charactervarying(64) | city_name | character varying(64) | confirm | boolean | country_code | character varying(2) | country_name | character varying(64) | destrank | integer | dstflag | boolean | enddst | timestamp without time zone| fr_airport_name | character varying(256) | fr_city_name | character varying(64) | fr_country_code | charactervarying(2) | fr_country_name | character varying(64) | fr_prov_state | character varying(64) | origrank | integer | prov_code | character varying(100) | prov_name | charactervarying(100) | prov_state | character varying(64) | startdst | timestamp without time zone| timeoffset | character varying(100) | timeref | character varying(100) | Indexes: "masterairportlist_pkey" PRIMARY KEY, btree (id) "idx_dest_cityname_conf" btree (city_name text_pattern_ops, destrank, confirm) "idx_orig_cityname_conf" btree (city_name text_pattern_ops, origrank, confirm)
On Fri, May 23, 2014 at 06:28:28AM -0700, David G Johnston wrote: > Bruce Momjian wrote > > On Thu, May 22, 2014 at 09:20:38AM -0600, Jeff Ross wrote: > >> >I just tested ALTER TABLE in 8.4 and it does create a toast table for > >> >this case in 9.4: > >> > > >> > CREATE TABLE test (x CHAR(10)); > >> > ALTER TABLE test ALTER COLUMN x TYPE CHAR(8000); > >> > > >> I just tried this on the problem table and it did indeed create a > >> toast table. > >> > >> I then retried pg_upgrade and it failed with the same problem on a > >> different table in the same database. Of the 67 databases in the > >> 8.4 cluster, 5 (so far) have had this problem on at least one table. > > > > Yeah, it would be nice to be able to report all the problem tables, but > > I don't know how to do that except from pg_upgrade failing. Is there > > anything similar about these tables? > > Would a toast table in this situation have to be empty on the 8.4 database? > Is there some kind of stat table query that would identify all such toast > tables? Although it is possible some of those tables do indeed need a toast > table but never make use of it (especially if one makes judicious use of > unlimited text columns but never fills them with large amounts of data - > like for lookup tables). I don't see that as helping here. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
On Fri, May 23, 2014 at 08:32:35AM -0600, Jeff Ross wrote: > > On 5/23/14, 7:21 AM, Bruce Momjian wrote: > > > >On Thu, May 22, 2014 at 09:20:38AM -0600, Jeff Ross wrote: > >>>I just tested ALTER TABLE in 8.4 and it does create a toast table for > >>>this case in 9.4: > >>> > >>> CREATE TABLE test (x CHAR(10)); > >>> ALTER TABLE test ALTER COLUMN x TYPE CHAR(8000); > >>> > >>I just tried this on the problem table and it did indeed create a > >>toast table. > >> > >>I then retried pg_upgrade and it failed with the same problem on a > >>different table in the same database. Of the 67 databases in the > >>8.4 cluster, 5 (so far) have had this problem on at least one table. > > > >Yeah, it would be nice to be able to report all the problem tables, but > >I don't know how to do that except from pg_upgrade failing. Is there > >anything similar about these tables? > > > > > Here are the last 2 tables I had a problem with: Both have character varying fields, which supports the idea that the field length might have been modified in pg_attribute. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
On Fri, May 23, 2014 at 03:36:20PM +0200, Andres Freund wrote: > On 2014-05-22 09:20:38 -0600, Jeff Ross wrote: > > On 5/21/14, 2:37 PM, Bruce Momjian wrote: > > >The only item I can think of that would cause this is someone changing > > >the length of a string. Did someone modify pg_attribute directly to > > >increase the length of one of the character columns? > > > I don't know, sorry. > > > >I just tested ALTER TABLE in 8.4 and it does create a toast table for > > >this case in 9.4: > > > > > > CREATE TABLE test (x CHAR(10)); > > > ALTER TABLE test ALTER COLUMN x TYPE CHAR(8000); > > > > > I just tried this on the problem table and it did indeed create a toast > > table. > > > > I then retried pg_upgrade and it failed with the same problem on a different > > table in the same database. Of the 67 databases in the 8.4 cluster, 5 (so > > far) have had this problem on at least one table. > > Any chance you could, *before* you create the toast table, do a: > SELECT attrelid::regclass, attname, attnum, attlen, * > FROM pg_attribute > WHERE attrelid = 'a'::regclass > ORDER BY attnum ASC; > > Where 'a' is replaced by the affected table? That's an interesting idea. In binary_upgrade mode, if we create a TOAST table and were _not_ passed in a toast id, we could detect that, though it just means we detect the failure earlier. Seeing as this is the first such report, I am not included to see that as a win. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Hi, On 2014-05-23 08:23:57 -0600, Jeff Ross wrote: > UDB=# \x > Expanded display is on. > UDB=# SELECT attrelid::regclass, attname, attnum, attlen, * > FROM pg_attribute > WHERE attrelid = 'masterairportlist'::regclass > ORDER BY attnum ASC; > UDB=# > [ RECORD 1 ]-+------------------ > ... A quick sum over the returned values seems to indicate that it's too large to not have a toast table. Adding up attlen and atttypmod gives a value of 1283. Considering that there additionally has to be VARHDRSZ space for the varlena header and that the maximum storage size for a varchar(n) is n * pg_encoding_max_length(GetDatabaseEncoding()) (usually 4) this seems to indicate that bad things[tm] have been done to the database. I suggest you write a script that does a 'ALTER TABLE $tbl ADD COLUMN toastme text; ALTER TABLE $tbl DROP COLUMN toastme' for all tables. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On 5/25/14, 11:44 AM, Andres Freund wrote: > > Hi, > > On 2014-05-23 08:23:57 -0600, Jeff Ross wrote: >> UDB=# \x >> Expanded display is on. >> UDB=# SELECT attrelid::regclass, attname, attnum, attlen, * >> FROM pg_attribute >> WHERE attrelid = 'masterairportlist'::regclass >> ORDER BY attnum ASC; >> UDB=# >> [ RECORD 1 ]-+------------------ >> ... > > A quick sum over the returned values seems to indicate that it's too > large to not have a toast table. Adding up attlen and atttypmod gives a > value of 1283. Considering that there additionally has to be VARHDRSZ > space for the varlena header and that the maximum storage size for a > varchar(n) is n * pg_encoding_max_length(GetDatabaseEncoding()) (usually > 4) this seems to indicate that bad things[tm] have been done to the > database. > I suggest you write a script that does a 'ALTER TABLE $tbl ADD COLUMN > toastme text; ALTER TABLE $tbl DROP COLUMN toastme' for all tables. > > Greetings, > > Andres Freund > Hi Andres, Yes, that is exactly what I will do before our next test migration. I'd already started on the script since I could not see any downside to adding a column big enough to force a toast table and then dropping it, exactly as Bruce and you suggest and especially if that will let me use pg_upgrade rather than the traditional dump/restore. Could a check like this be added to pg_upgrade? Is there a downside to adding a column big enough to force a toast table and then dropping it for any table that is too large not to have a toast table but doesn't? Jeff
Hi, On 2014-05-25 16:59:24 -0600, Jeff Ross wrote: > Could a check like this be added to pg_upgrade? > Is there a downside to > adding a column big enough to force a toast table and then dropping it for > any table that is too large not to have a toast table but doesn't? It can take time and permanently 'uses up' a attribute number. So you'd add costs for everyone for the few people that decided to do something dangerous and unsupported... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services