BUG #5064: not-null constraints is not inherited

The following bug has been logged online:

Bug reference: 5064
Logged by: Tomonari Katsumata
Email address: katsumata(dot)tomonari(at)oss(dot)ntt(dot)co(dot)jp
PostgreSQL version: 8.5dev, 8.4.1
Operating system: Windows XP, RHEL 5.2
Description: not-null constraints is not inherited
Details:

Hi,

I found a bug about inheritance.
The user manual says, "All check constraints and not-null constraints on a
parent table are automatically inherited by its children."
But, the behavier is not true. The sample is like below.

postgres=# CREATE TABLE parent (id integer);
postgres=# CREATE TABLE child () inherits (parent);
postgres=# ALTER TABLE parent ADD PRIMARY KEY (id);
postgres=# \d+ parent
Table "public.parent"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
id | integer | not null | plain |
Indexes:
"parent_pkey" PRIMARY KEY, btree (id)
Child tables: child
Has OIDs: no

postgres=# \d+ child
Table "public.child"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
id | integer | 　 | plain |
Inherits: parent
Has OIDs: no

child table does not have a "not null" constraints.
I think it's not desirable behavier.

On the other hand, if I set a constraints using
"ALTER COLUMN SET NOT NULL", the constraints is inherited by its child.

postgres=# DROP TABLE parent, child;
postgres=# CREATE TABLE parent (id integer);
postgres=# CREATE TABLE child () inherits (parent);
postgres=# ALTER TABLE parent ALTER COLUMN id SET NOT NULL;
postgres=# \d+ parent
Table "public.parent"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
id | integer | not null | plain |
Child tables: child
Has OIDs: no

postgres=# \d+ child
Table "public.child"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
id | integer | not null | plain |
Inherits: parent
Has OIDs: no

regards,
--
Tomonari Katsumata
katsumata(dot)tomonari(at)oss(dot)ntt(dot)co(dot)jp

"Tomonari Katsumata" <katsumata(dot)tomonari(at)oss(dot)ntt(dot)co(dot)jp> writes:
> postgres=# CREATE TABLE parent (id integer);
> postgres=# CREATE TABLE child () inherits (parent);
> postgres=# ALTER TABLE parent ADD PRIMARY KEY (id);

> child table does not have a "not null" constraints.
> I think it's not desirable behavier.

The code says:

* XXX: Shouldn't the ALTER TABLE .. SET NOT NULL cascade to child
* tables? Currently, since the PRIMARY KEY itself doesn't cascade,
* we don't cascade the notnull constraint(s) either; but this is
* pretty debatable.

Given that we've tightened things up to insist that child tables have
all the check constraints that the parent does, it seems like it might
be desirable to make the same rule for NOT NULL constraints. However,
while changing DefineIndex for this would be a one-liner change, there
are a lot of other implications of such a rule. This would have to
be rejected:
alter table only parent alter id set not null;
which again isn't a big deal. What is a big deal is that we'd also
have to forbid ALTER DROP NOT NULL on the child. For that, we'd need
to track whether NOT NULL constraints were inherited or not. There
is already a TODO item about that. I've added this thread to the
TODO entry.

regards, tom lane