Re: ALTER TABLE...ALTER COLUMN vs inheritance

Bernd Helmle <mailings(at)oopsware(dot)de> writes:
> My first idea is to just introduce a special contype in pg_constraint
> representing a NOT NULL constraint on a column, which holds all required
> information to do the mentioned maintenance stuff on them and to keep most
> of the current infrastructure. Utility commands need to track all changes
> in pg_constraint and keep pg_attribute.attnotnull up to date.

> Another possibility is to change the representation of NOT NULL to be a
> CHECK constraint (e.g. CHECK(col IS NOT NULL)) internally and leave all the
> responsibility up to the current existing check constraint infrastructure
> (which already does the right thing for inheritance, e.g. it's not possible
> to drop such a constraint if it was inherited).

I'd go for the first of those, for sure. Testing attnotnull is
significantly cheaper than enforcing a generic constraint expression,
and NOT NULL is a sufficiently common case to be worth worrying about
optimizing it. Furthermore, removing attnotnull would break an unknown
but probably not-negligible amount of client-side code that cares
whether columns are known not null (I think both JDBC and ODBC track
that). And it would significantly complicate code in the backend that
wants to determine whether a column is known not null --- there are
several proposals for planner optimizations that would depend on knowing
that, for example.

You will find yourself copying-and-pasting a fair amount of the
check-constraint code if you implement this as a completely separate
code path, and so it might be worthwhile to be creative about exactly
what the pg_constraint representations look like --- maybe you can
design it to share some of that code. But I recommend strongly that
attnotnull stay there.

regards, tom lane