Hi all,

A couple of months back the $subject has been mentioned, though nobody
actually wrote a patch to prevent that:
So here is one..

To put it short, it should not be possible to drop a NOT NULL
constraint on a child relation when its parent table is using it, this
should only be possible from the parent. Attached is a patch handling
this problem by adding a new function in pg_inherits.c to fetch the
list of parent relations for a given relation OID, and did some
refactoring to stick with what is done when scanning child relations.
And here is what this patch can do:
=# create table parent (a int not null);
=# create table child (a int not null);
=# alter table child inherit parent ;
=# alter table child alter COLUMN a drop not null;  -- would work on HEAD
ERROR:  42P16: cannot drop NOT NULL constraint for attribute "a"
DETAIL:  The same column in parent relation "parent" is marked NOT NULL
LOCATION:  ATExecDropNotNull, tablecmds.c:5281
=# alter table parent  alter COLUMN a drop not null; -- works on parent
=# \d child
     Table "public.child"
 Column |  Type   | Modifiers
 a      | integer |
Inherits: parent

I have added a new index to pg_inherits, so that's not something that
could be back-patched, still it would be good to fix this weird
behavior on HEAD. I am adding that to the next CF.

Attachment: child-drop-not-null-v1.patch
Description: invalid/octet-stream

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to