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. +