--On 4. November 2009 09:57:27 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I think the
> consensus was that the way to fix this (along with some other problems)
> is to start representing NOT NULL constraints in pg_constraint, turning
> attnotnull into just a bit of denormalization for performance.
I've just started looking into this and wonder how this should look like.
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).
ALTER TABLE ... SET NOT NULL and DROP NOT NULL will be just syntactic sugar
then.
I don't know the original design decisions for the current representation,
but it seems it wasn't essential?
Of course, there's still the requirement to special case those check
constraints in various places, since pg_dump and psql have to do the right
thing.
--
Thanks
Bernd