Horton

Thread

  1. pg_upgrade relation OID mismatches

    Peter Eisentraut <peter_e@gmx.net> — 2011-11-22T20:02:07Z

    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

  2. Re: pg_upgrade relation OID mismatches

    Bruce Momjian <bruce@momjian.us> — 2011-11-22T20:42:35Z

    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. +

  3. Re: pg_upgrade relation OID mismatches

    Peter Eisentraut <peter_e@gmx.net> — 2011-11-22T21:56:55Z

    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     |

  4. Re: pg_upgrade relation OID mismatches

    Bruce Momjian <bruce@momjian.us> — 2011-11-22T23:29:13Z

    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. +

  5. Re: pg_upgrade relation OID mismatches

    Bruce Momjian <bruce@momjian.us> — 2011-11-24T05:01:40Z

    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. +

  6. Re: pg_upgrade relation OID mismatches

    Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> — 2011-11-24T13:07:51Z

    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

  7. Re: pg_upgrade relation OID mismatches

    Bruce Momjian <bruce@momjian.us> — 2011-11-24T14:47:46Z

    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. +

  8. Re: pg_upgrade relation OID mismatches

    Bruce Momjian <bruce@momjian.us> — 2011-11-24T15:26:41Z

    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. +

  9. Re: pg_upgrade relation OID mismatches

    Bruce Momjian <bruce@momjian.us> — 2011-11-25T20:05:09Z

    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. +

  10. Re: pg_upgrade relation OID mismatches

    Alvaro Herrera <alvherre@commandprompt.com> — 2011-11-25T20:26:24Z

    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