Re: 7.1 (current) unwanted NOT NULL constraint inserted

OK, I see the problem. You have:

CREATE TABLE person (
id CHAR(10)
);

CREATE TABLE organisation (
...,
PRIMARY KEY (id)
)
INHERITS (person);

ie, a PRIMARY KEY declaration on an inherited column. Normally a
PRIMARY KEY declaration causes the key column to become marked
NOT NULL --- but if the key column is an inherited one then the
code misapplies the mark to the last non-inherited column ... or
coredumps if there are no non-inherited columns :-(. See line 995
in parse/analyze.c.

While it's easy enough to avoid the mis-marking of the last column,
causing the right thing to happen instead is much less easy. What
we really want is for the key column to be marked NOT NULL,
but during analyze.c there isn't a set of ColumnDefs for the inherited
columns, and so there's no place to put the mark.

Short of a major restructuring of inherited-column creation, I see
no good solution to this. I see two bad solutions:

1. Require that the referenced column be marked NOT NULL already,
so that the constraint will be inherited properly from the parent.
In other words you couldn't say PRIMARY KEY for an inherited column
unless it is NOT NULL (or a fortiori, PRIMARY KEY) in the parent table.

2. Do nothing, in effect silently dropping the NOT NULL constraint
for such a column. (Actually we don't have to be silent about it;
we could emit a NOTICE when the parent doesn't have NOT NULL.)

IMHO, #1 is a little less bad, but I'm not firmly committed to it.
Comments anyone?

regards, tom lane