BUG #3973: pg_dump using inherited tables do not always restore

"Alex Hunsaker" <badalex@gmail.com> writes:

create table junk (val integer not null, val2 integer);
create table junk_child () inherits (junk_1);
alter table junk_child alter column val drop not null;
insert into junk_child (val2) values (1);

pg_dump -t junk -t junk_child

pg_restore/psql will fail because junk_child.val now has a not null
constraint

Actually the bug is that ALTER TABLE allows you to do that. It should
not be possible to drop an inherited constraint, but right now there's
not enough information in the system catalogs to detect the situation.
Fixing this has been on the TODO list for awhile:

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

regards, tom lane

On Wed, Feb 20, 2008 at 3:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Alex Hunsaker" <badalex@gmail.com> writes:

create table junk (val integer not null, val2 integer);
create table junk_child () inherits (junk_1);
alter table junk_child alter column val drop not null;
insert into junk_child (val2) values (1);

pg_dump -t junk -t junk_child

pg_restore/psql will fail because junk_child.val now has a not null
constraint

Actually the bug is that ALTER TABLE allows you to do that. It should
not be possible to drop an inherited constraint, but right now there's
not enough information in the system catalogs to detect the situation.
Fixing this has been on the TODO list for awhile:

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

regards, tom lane

Hrm how about something like the attached patch?

It only handles set not null/drop not null. And I thought about
making it so set default behaved the same way, but i can see how that
can be useful in the real world. Thoughts?

Arguably pg_dump should just do something similar to what it does for
set default (because that dumps correctly)... I only say that because
there specific regressions test for the behavior I outlined above.
Which is now "broken" with my patch.

Be gentle... its my first dive into postgresql guts...

"Alex Hunsaker" <badalex@gmail.com> writes:

On Wed, Feb 20, 2008 at 3:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Actually the bug is that ALTER TABLE allows you to do that. It should
not be possible to drop an inherited constraint, but right now there's
not enough information in the system catalogs to detect the situation.
Fixing this has been on the TODO list for awhile:

Hrm how about something like the attached patch?

It seems much more restrictive than necessary, plus it does nothing
for the check-constraint case. My recollection of the previous
discussion about how to fix this was that we needed to add an inhcount
column to pg_constraint, and add entries for not-null constraints (at
least inherited ones) to pg_constraint so that they'd be able to have
inhcount fields. The latter would also allow us to attach names to
not-null constraints, which I think is required by spec but we've never
supported.

regards, tom lane

<snip>

It seems much more restrictive than necessary, plus it does nothing
for the check-constraint case. My recollection of the previous
discussion about how to fix this was that we needed to add an inhcount
column to pg_constraint, and add entries for not-null constraints (at
least inherited ones) to pg_constraint so that they'd be able to have
inhcount fields. The latter would also allow us to attach names to
not-null constraints, which I think is required by spec but we've never
supported.

regards, tom lane

Ok I found some time to look at what would be involved in that...
Seems doable. Ill see what I can whip up in the next month. (Im time
pressed, who isn't though) Ill just post whatever i come up with (if
and when) to psql-patches.