Thread: Re: [GENERAL] possible bug with inheritance?
Bruce Momjian wrote: > For primary key, there is no enforcement of the primary key, e.g.: > > test=> CREATE TABLE parent (name TEXT); > CREATE TABLE > test=> CREATE TABLE child (age INT) inherits (parent) ; > CREATE TABLE > test=> ALTER TABLE parent ADD primary KEY (name); > NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index > "parent_pkey" for table "parent" > ALTER TABLE > test=> INSERT INTO parent (name) VALUES ('a'); > INSERT 0 1 > test=> INSERT INTO child (name) VALUES ('a'); > INSERT 0 1 > test=> SELECT * FROM parent; > name > ------ > a > a > (2 rows) > > So, it seems like this is the ugly truth of our inheritance limitations > with primary key, and unless we can fix the primary key issues with > inheritance, our current behavior is the more predictable we can hope for. [ Thread moved to hackers because this might be a valid bug. ] Summary: ALTER TABLE SET NOT NULL on a parent table is passed to the child, while ALTER TABLE ADD PRIMARY KEY is not, particularly the NOT NULL part of the PRIMARY KEY specification. OK, now I understand what you are getting at --- the following returns a NULL value from the parent: test=> CREATE TABLE parent (name text);CREATE TABLEtest=> CREATE TABLE child (age int) INHERITS (parent) ;CREATE TABLEtest=>ALTER TABLE parent ADD PRIMARY KEY (name);NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index"parent_pkey"for table "parent"ALTER TABLEtest=> INSERT INTO child (name) VALUES (null);INSERT 0 1test=> \pset null'(null)'Null display is "(null)".test=> SELECT * FROM parent; name-------- (null)(1 row) while the parent has a NOT NULL specification: test=> \d parent Table "public.parent" Column | Type | Modifiers--------+------+----------- name | text | not nullIndexes: "parent_pkey" PRIMARY KEY, btree (name)Number of child tables: 1 (Use \d+ to list them.) That does seem like something that should be fixed. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> wrote: > Summary: ALTER TABLE SET NOT NULL on a parent table is passed to the > child, while ALTER TABLE ADD PRIMARY KEY is not, particularly the NOT > NULL part of the PRIMARY KEY specification. > > That does seem like something that should be fixed. Yeah, the issue is in our TODO list: http://wiki.postgresql.org/wiki/Todo | Move NOT NULL constraint information to pg_constraint | Currently NOT NULL constraints are stored in pg_attribute without | any designation of their origins, e.g. primary keys. One manifest | problem is that dropping a PRIMARY KEY constraint does not remove | the NOT NULL constraint designation. Another issue is that we should | probably force NOT NULL to be propagated from parent tables to children, | just as CHECK constraints are. (But then does dropping PRIMARY KEY | affect children?) And the same bug report has been here: http://archives.postgresql.org/message-id/200909181005.n8IA5Ris061239@wwwmaster.postgresql.org | BUG #5064: not-null constraints is not inherited Regards, --- Takahiro Itagaki NTT Open Source Software Center
Takahiro Itagaki wrote: > > Bruce Momjian <bruce@momjian.us> wrote: > > > Summary: ALTER TABLE SET NOT NULL on a parent table is passed to the > > child, while ALTER TABLE ADD PRIMARY KEY is not, particularly the NOT > > NULL part of the PRIMARY KEY specification. > > > > That does seem like something that should be fixed. > > Yeah, the issue is in our TODO list: > http://wiki.postgresql.org/wiki/Todo > | Move NOT NULL constraint information to pg_constraint > | Currently NOT NULL constraints are stored in pg_attribute without > | any designation of their origins, e.g. primary keys. One manifest > | problem is that dropping a PRIMARY KEY constraint does not remove > | the NOT NULL constraint designation. Another issue is that we should > | probably force NOT NULL to be propagated from parent tables to children, > | just as CHECK constraints are. (But then does dropping PRIMARY KEY > | affect children?) > > And the same bug report has been here: > http://archives.postgresql.org/message-id/200909181005.n8IA5Ris061239@wwwmaster.postgresql.org > | BUG #5064: not-null constraints is not inherited Ah, I see it on the TODO list now. Thanks. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +