Problem with dump/restore and inheritance

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

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.

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

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