Thread: Problem with dump/restore and inheritance

G'day all,

PG version: 8.1.0  (also 7.4.9)
OS: Linux (debian/testing)

Restoring a database with inherited tables can result in an
incorrect schema (and therefore inability to restore data).

E.g. using the script below, the 'bar.f1' column in the 'new'
database ends up with a 'not null' constraint that isn't present
in the 'orig' database.

Cheers,

Chris.

----------------------------------------------------------------------
#!/bin/sh
createdb orig
createdb new
psql orig <<END
  create table foo (f1 integer not null);
  create table bar () inherits(foo);
  alter table bar alter column f1 drop not null;
END
pg_dump orig | psql new
----------------------------------------------------------------------

Chris Dunlop <chris@onthe.net.au> writes:
> E.g. using the script below, the 'bar.f1' column in the 'new'
> database ends up with a 'not null' constraint that isn't present
> in the 'orig' database.

>   create table foo (f1 integer not null);
>   create table bar () inherits(foo);
>   alter table bar alter column f1 drop not null;

The general consensus is that the above should be illegal, ie, the ALTER
should have been rejected.  Otherwise you would have a situation where a
"SELECT FROM foo" could return nulls, violating the very clear contract
of that table.  We have not got around to enforcing this yet, but it's
on the TODO.  I don't see it as a pg_dump bug that it's unable to
reproduce an invalid situation.

            regards, tom lane

Chris Dunlop <chris@onthe.net.au> writes:
> One way or the other, I think either allowing the inherited
> constraints to be dropped, or the inability of pg_dump to
> correctly dump the resulting schema, should be considered a bug
> rather than a lacking feature, as the current situation results
> in problematical restores.  Is there a "known bugs" list?

I agree that allowing inherited constraints to be dropped is a bug.
We don't really have a "known bugs" list other than the TODO list,
which presently includes

    o %Disallow dropping of an inherited constraint
    o %Prevent child tables from altering or dropping constraints
          like CHECK that were inherited from the parent table

(Which looks a bit redundant to me, but that's what Bruce has listed.)

            regards, tom lane

On Wed, Feb 22, 2006 at 10:11:51AM -0500, Tom Lane wrote:
> Chris Dunlop <chris@onthe.net.au> writes:
>> E.g. using the script below, the 'bar.f1' column in the 'new'
>> database ends up with a 'not null' constraint that isn't present
>> in the 'orig' database.
>
>>   create table foo (f1 integer not null);
>>   create table bar () inherits(foo);
>>   alter table bar alter column f1 drop not null;
>
> The general consensus is that the above should be illegal, ie,
> the ALTER should have been rejected.  Otherwise you would have
> a situation where a "SELECT FROM foo" could return nulls,
> violating the very clear contract of that table.  We have not
> got around to enforcing this yet, but it's on the TODO.  I
> don't see it as a pg_dump bug that it's unable to reproduce an
> invalid situation.

OK, thanks for the response Tom.  That makes sense (although it
could also be argued the contract is maintained using the "ONLY"
clause - but I imagine this has been beaten to death on the
lists already).

We'll redo our schema and program logic to be prepared for this
change if/when it comes about.  At least this will allow us to
correctly restore this one database without fooling with the
dump file!

One way or the other, I think either allowing the inherited
constraints to be dropped, or the inability of pg_dump to
correctly dump the resulting schema, should be considered a bug
rather than a lacking feature, as the current situation results
in problematical restores.  Is there a "known bugs" list?


Cheers,

Chris.

Tom Lane wrote:
> Chris Dunlop <chris@onthe.net.au> writes:
> > One way or the other, I think either allowing the inherited
> > constraints to be dropped, or the inability of pg_dump to
> > correctly dump the resulting schema, should be considered a bug
> > rather than a lacking feature, as the current situation results
> > in problematical restores.  Is there a "known bugs" list?
>
> I agree that allowing inherited constraints to be dropped is a bug.
> We don't really have a "known bugs" list other than the TODO list,
> which presently includes
>
>     o %Disallow dropping of an inherited constraint
>     o %Prevent child tables from altering or dropping constraints
>           like CHECK that were inherited from the parent table
>
> (Which looks a bit redundant to me, but that's what Bruce has listed.)

Thanks, first one removed.

--
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +