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

On Wed, Feb 20, 2008 at 3:55 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Alex Hunsaker" <badalex(at)gmail(dot)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...