Re: possible bug with inheritance?

Hi,

Our documentation says:
"All check constraints and not-null constraints on a parent table are
automatically inherited by its children."

Okay, this works as expected:

test=# create table parent (name text primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "parent_pkey" for table "parent"
CREATE TABLE
test=*# create table child (age int) inherits (parent) ;
CREATE TABLE
test=*# \d child
Table "public.child"
Column | Type | Modifiers
--------+---------+-----------
name | text | not null
age | integer |
Inherits: parent

Nice, the 'not null' constraint is in the child-table.

test=*# rollback;
ROLLBACK
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=*# \d child
Table "public.child"
Column | Type | Modifiers
--------+---------+-----------
name | text |
age | integer |
Inherits: parent

Doh, there isn't the 'not null' - modifier. The parent-table contains
this modifier as part of the primary key - definition.

Other test:

test=# create table parent (name text);
CREATE TABLE
test=*# create table child (age int) inherits (parent) ;
CREATE TABLE
test=*# alter table parent alter column name set not null;
ALTER TABLE
test=*# \d child
Table "public.child"
Column | Type | Modifiers
--------+---------+-----------
name | text | not null
age | integer |
Inherits: parent

Okay, we have again the 'not null'.

I think, that's not really clearly. In some case the 'not null' -
constraint are in the child-table, in other case they are not.

Version 8.4.2.

Bug, feature? What have i overlooked?

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99

Wow, you are right that this is really weird. I think the fundamental
issue is that PRIMARY KEY does not pass down to the child (and hence NOT
NULL doesn't either), while NOT NULL does pass to the child. A larger
question is why PRIMARY KEY doesn't pass to the child. If there is a
good reason for that, there is a good reason that the NOT NULL that is
part of PRIMARY KEY doesn't pass to the child.

I think the ALTER TABLE manual page might have a hint:

The columns must have matching data types, and if they have
NOT NULL constraints in the parent then they must also have
NOT NULL constraints in the child.

What I think is happening is that the NOT NULL specification is a
qualification of what data values can be returned from the parent (i.e.
no nulls), and that has to be passed to the child so a query on the
parent with a not null column doesn't return null values from a child
that contains nulls in that column.

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.

---------------------------------------------------------------------------

A. Kretschmer wrote:
> Hi,
>
> Our documentation says:
> "All check constraints and not-null constraints on a parent table are
> automatically inherited by its children."
>
> Okay, this works as expected:
>
> test=# create table parent (name text primary key);
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "parent_pkey" for table "parent"
> CREATE TABLE
> test=*# create table child (age int) inherits (parent) ;
> CREATE TABLE
> test=*# \d child
> Table "public.child"
> Column | Type | Modifiers
> --------+---------+-----------
> name | text | not null
> age | integer |
> Inherits: parent
>
>
> Nice, the 'not null' constraint is in the child-table.
>
>
> test=*# rollback;
> ROLLBACK
> 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=*# \d child
> Table "public.child"
> Column | Type | Modifiers
> --------+---------+-----------
> name | text |
> age | integer |
> Inherits: parent
>
>
> Doh, there isn't the 'not null' - modifier. The parent-table contains
> this modifier as part of the primary key - definition.
>
>
> Other test:
>
> test=# create table parent (name text);
> CREATE TABLE
> test=*# create table child (age int) inherits (parent) ;
> CREATE TABLE
> test=*# alter table parent alter column name set not null;
> ALTER TABLE
> test=*# \d child
> Table "public.child"
> Column | Type | Modifiers
> --------+---------+-----------
> name | text | not null
> age | integer |
> Inherits: parent
>
>
> Okay, we have again the 'not null'.
>
>
> I think, that's not really clearly. In some case the 'not null' -
> constraint are in the child-table, in other case they are not.
>
> Version 8.4.2.
>
> Bug, feature? What have i overlooked?
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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 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 child (name) VALUES (null);
INSERT 0 1
test=> \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 null
Indexes:
"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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)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(at)momjian(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +