Hi all, A couple of months back the $subject has been mentioned, though nobody actually wrote a patch to prevent that: http://www.postgresql.org/message-id/21633.1448383...@sss.pgh.pa.us 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
=# create table child (a int not null);
CREATE TABLE
=# alter table child inherit parent ;
ALTER TABLE
=# 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
ALTER TABLE
=# \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.
Regards,
--
Michael
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: http://www.postgresql.org/mailpref/pgsql-hackers
