Thread: Re: [BUGS] BUG #3973: pg_dump using inherited tables do not always restore

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.