Re: ALTER TABLE...ALTER COLUMN vs inheritance

--On 4. November 2009 09:57:27 -0500 Tom Lane  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