Обсуждение: pg_upgrade relation OID mismatches
I thought these were fixed a while ago, but I'm still seeing these when upgrading from master to self (using testing script sent in a while ago). This is completely reproducible. What's happening? ... Restoring user relation files /home/peter/devel/postgresql/git/postgresql/contrib/pg_upgra Mismatch of relation OID in database "regression": old OID 16701, new OID 16689 Failure, exiting
Peter Eisentraut wrote: > I thought these were fixed a while ago, but I'm still seeing these when > upgrading from master to self (using testing script sent in a while > ago). This is completely reproducible. What's happening? > > ... > Restoring user relation files > /home/peter/devel/postgresql/git/postgresql/contrib/pg_upgra > Mismatch of relation OID in database "regression": old OID 16701, new OID 16689 > Failure, exiting Yes, I certainly thought they were all addressed. What object is 16701 in the old database? Anything unusual about it? This is saying the relation oid was not preserved. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On tis, 2011-11-22 at 15:42 -0500, Bruce Momjian wrote: > Peter Eisentraut wrote: > > I thought these were fixed a while ago, but I'm still seeing these when > > upgrading from master to self (using testing script sent in a while > > ago). This is completely reproducible. What's happening? > > > > ... > > Restoring user relation files > > /home/peter/devel/postgresql/git/postgresql/contrib/pg_upgra > > Mismatch of relation OID in database "regression": old OID 16701, new OID 16689 > > Failure, exiting > > Yes, I certainly thought they were all addressed. What object is 16701 > in the old database? Anything unusual about it? This is saying the > relation oid was not preserved. It came in with the range types feature: + psql -d regression -x -c 'select * from pg_class where oid = 16701' -[ RECORD 1 ]--+---------------- relname | test_range_gist relnamespace | 2200 reltype | 16703 reloftype | 0 relowner | 10 relam | 0 relfilenode | 16701 reltablespace | 0 relpages | 33 reltuples | 6200 relallvisible | 33 reltoastrelid | 16704 reltoastidxid | 0 relhasindex | t relisshared | f relpersistence | p relkind | r relnatts | 1 relchecks | 0 relhasoids | f relhaspkey | f relhasrules | f relhastriggers | f relhassubclass | f relfrozenxid | 1627 relacl | reloptions |
Peter Eisentraut wrote: > On tis, 2011-11-22 at 15:42 -0500, Bruce Momjian wrote: > > Peter Eisentraut wrote: > > > I thought these were fixed a while ago, but I'm still seeing these when > > > upgrading from master to self (using testing script sent in a while > > > ago). This is completely reproducible. What's happening? > > > > > > ... > > > Restoring user relation files > > > /home/peter/devel/postgresql/git/postgresql/contrib/pg_upgra > > > Mismatch of relation OID in database "regression": old OID 16701, new OID 16689 > > > Failure, exiting > > > > Yes, I certainly thought they were all addressed. What object is 16701 > > in the old database? Anything unusual about it? This is saying the > > relation oid was not preserved. > > It came in with the range types feature: > > + psql -d regression -x -c 'select * from pg_class where oid = 16701' > -[ RECORD 1 ]--+---------------- > relname | test_range_gist > relnamespace | 2200 > reltype | 16703 > reloftype | 0 > relowner | 10 > relam | 0 > relfilenode | 16701 > reltablespace | 0 > relpages | 33 > reltuples | 6200 > relallvisible | 33 > reltoastrelid | 16704 > reltoastidxid | 0 > relhasindex | t > relisshared | f > relpersistence | p > relkind | r > relnatts | 1 > relchecks | 0 > relhasoids | f > relhaspkey | f > relhasrules | f > relhastriggers | f > relhassubclass | f > relfrozenxid | 1627 > relacl | > reloptions | OK, that is a heap table. My only guess is that the heap is being created without binary_upgrade_next_heap_pg_class_oid being set. Looking at the code, I can't see how the heap could be created without this happening. Another idea is that pg_dumpall isn't output the proper value, but again, how is this data type different from the others. I will try to research this further. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian wrote: > OK, that is a heap table. My only guess is that the heap is being > created without binary_upgrade_next_heap_pg_class_oid being set. > Looking at the code, I can't see how the heap could be created without > this happening. Another idea is that pg_dumpall isn't output the proper > value, but again, how is this data type different from the others. I have reproduced the failure and found it was code I added to pg_dump back in 9.0. The code didn't set the index oid for exclusion constraint indexes. Once these were added to the regression tests for range types recently, pg_upgrade threw an error. My assumption is that anyone trying to use an exclusion constraint with pg_upgrade will get the same type of error. Patch attached. Should it be backpatched to 9.0 and 9.1? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c new file mode 100644 index 644637c..6dc3d40 *** a/src/bin/pg_dump/pg_dump.c --- b/src/bin/pg_dump/pg_dump.c *************** dumpConstraint(Archive *fout, Constraint *** 12926,12932 **** exit_nicely(); } ! if (binary_upgrade && !coninfo->condef) binary_upgrade_set_pg_class_oids(q, indxinfo->dobj.catId.oid, true); appendPQExpBuffer(q, "ALTER TABLE ONLY %s\n", --- 12926,12932 ---- exit_nicely(); } ! if (binary_upgrade) binary_upgrade_set_pg_class_oids(q, indxinfo->dobj.catId.oid, true); appendPQExpBuffer(q, "ALTER TABLE ONLY %s\n",
On 24.11.2011 07:01, Bruce Momjian wrote: > Bruce Momjian wrote: >> OK, that is a heap table. My only guess is that the heap is being >> created without binary_upgrade_next_heap_pg_class_oid being set. >> Looking at the code, I can't see how the heap could be created without >> this happening. Another idea is that pg_dumpall isn't output the proper >> value, but again, how is this data type different from the others. > > I have reproduced the failure and found it was code I added to pg_dump > back in 9.0. The code didn't set the index oid for exclusion constraint > indexes. Once these were added to the regression tests for range types > recently, pg_upgrade threw an error. > > My assumption is that anyone trying to use an exclusion constraint with > pg_upgrade will get the same type of error. > > Patch attached. Should it be backpatched to 9.0 and 9.1? If I understood correctly, pg_upgrade of a database with exclusion constraints won't work without this patch? In that case, it should be backpatched. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas wrote: > On 24.11.2011 07:01, Bruce Momjian wrote: > > Bruce Momjian wrote: > >> OK, that is a heap table. My only guess is that the heap is being > >> created without binary_upgrade_next_heap_pg_class_oid being set. > >> Looking at the code, I can't see how the heap could be created without > >> this happening. Another idea is that pg_dumpall isn't output the proper > >> value, but again, how is this data type different from the others. > > > > I have reproduced the failure and found it was code I added to pg_dump > > back in 9.0. The code didn't set the index oid for exclusion constraint > > indexes. Once these were added to the regression tests for range types > > recently, pg_upgrade threw an error. > > > > My assumption is that anyone trying to use an exclusion constraint with > > pg_upgrade will get the same type of error. > > > > Patch attached. Should it be backpatched to 9.0 and 9.1? > > If I understood correctly, pg_upgrade of a database with exclusion > constraints won't work without this patch? In that case, it should be > backpatched. Yes, that is my guess. I will test it today or tomorrow. I am surprised we had _no_ exclusion constraint tests in the regression tests until now. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian wrote: > Heikki Linnakangas wrote: > > On 24.11.2011 07:01, Bruce Momjian wrote: > > > Bruce Momjian wrote: > > >> OK, that is a heap table. My only guess is that the heap is being > > >> created without binary_upgrade_next_heap_pg_class_oid being set. > > >> Looking at the code, I can't see how the heap could be created without > > >> this happening. Another idea is that pg_dumpall isn't output the proper > > >> value, but again, how is this data type different from the others. > > > > > > I have reproduced the failure and found it was code I added to pg_dump > > > back in 9.0. The code didn't set the index oid for exclusion constraint > > > indexes. Once these were added to the regression tests for range types > > > recently, pg_upgrade threw an error. > > > > > > My assumption is that anyone trying to use an exclusion constraint with > > > pg_upgrade will get the same type of error. > > > > > > Patch attached. Should it be backpatched to 9.0 and 9.1? > > > > If I understood correctly, pg_upgrade of a database with exclusion > > constraints won't work without this patch? In that case, it should be > > backpatched. > > Yes, that is my guess. I will test it today or tomorrow. I am > surprised we had _no_ exclusion constraint tests in the regression tests > until now. I do see EXCLUDE constraints in 9.0, so I need to do some more research: CREATE TABLE circles ( c1 CIRCLE, c2 TEXT, EXCLUDE USING gist (c1 WITH &&, (c2::circle) WITH &&) WHERE (circle_center(c1)<> '(0,0)')); It seems it is only the range-type EXCLUDE constraints that are causing a problem. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian wrote: > Bruce Momjian wrote: > > Heikki Linnakangas wrote: > > > On 24.11.2011 07:01, Bruce Momjian wrote: > > > > Bruce Momjian wrote: > > > >> OK, that is a heap table. My only guess is that the heap is being > > > >> created without binary_upgrade_next_heap_pg_class_oid being set. > > > >> Looking at the code, I can't see how the heap could be created without > > > >> this happening. Another idea is that pg_dumpall isn't output the proper > > > >> value, but again, how is this data type different from the others. > > > > > > > > I have reproduced the failure and found it was code I added to pg_dump > > > > back in 9.0. The code didn't set the index oid for exclusion constraint > > > > indexes. Once these were added to the regression tests for range types > > > > recently, pg_upgrade threw an error. > > > > > > > > My assumption is that anyone trying to use an exclusion constraint with > > > > pg_upgrade will get the same type of error. > > > > > > > > Patch attached. Should it be backpatched to 9.0 and 9.1? > > > > > > If I understood correctly, pg_upgrade of a database with exclusion > > > constraints won't work without this patch? In that case, it should be > > > backpatched. > > > > Yes, that is my guess. I will test it today or tomorrow. I am > > surprised we had _no_ exclusion constraint tests in the regression tests > > until now. > > I do see EXCLUDE constraints in 9.0, so I need to do some more research: > > CREATE TABLE circles ( > c1 CIRCLE, > c2 TEXT, > EXCLUDE USING gist > (c1 WITH &&, (c2::circle) WITH &&) > WHERE (circle_center(c1) <> '(0,0)') > ); > > It seems it is only the range-type EXCLUDE constraints that are causing > a problem. OK, it turns out that exclusion contraints used in pre-9.2 regression tests were deleted before the regression tests finished, which means they were not tested by me. (This might be a good reason _not_ to have the regression tests removing objects before the scripts exit.) I have applied the posted patch to 9.0, 9.1, and 9.2. The good news is that no one reported this failure in the field, and it would have generated an error, rather than silently failing. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Excerpts from Bruce Momjian's message of vie nov 25 17:05:09 -0300 2011: > OK, it turns out that exclusion contraints used in pre-9.2 regression > tests were deleted before the regression tests finished, which means > they were not tested by me. (This might be a good reason _not_ to have > the regression tests removing objects before the scripts exit.) Yeah, I vote for most/all tests keeping their objects instead of dropping them, where sensible. Also, some tests use a single table name and create it and drop it just to create it again later with different properties (I think the alter_table test is like this). We should avoid that. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support