Re: ALTER TABLE...ALTER COLUMN vs inheritance

--On 4. November 2009 09:57:27 -0500 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Yeah, this is a known issue. The ALTER should be rejected, but it is
> not, because we don't have enough infrastructure to notice that the
> constraint is inherited and logically can't be dropped. 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.

Lost it a little from my radar, but here's is a first shot on this issue
now. The patch creates a new CONSTRAINT_NOTNULL contype and assigns all
required information for the NOT NULL constraint to it. Currently the
constraint records the attribute number it belongs to and manages the
inheritance properties. Passes regression tests with some adjustments to
pg_constraint output.

The patch as it stands employs a dedicated code path for
ATExecDropNotNull(), thus duplicates the behavior of
ATExecDropConstraint(). I'm not really satisfied with this, but i did it
this way to prevent some heavy conditional rearrangement in
ATExecDropConstraint(). Maybe its worth to move the code to adjust
constraint inheritance properties into a separate function.

There's also a remaining issue which needs to be addressed: currently
pg_get_constraintdef_worker() is special case'd to dump the correct syntax
for the NOT NULL constraint, which is totally redundant. I'm not sure how
to do it correctly, since for example ATExecAlterColumnType() actually uses
it to restore dependent constraints on a column. We might want to just move
the special case there.

I understand that people are busy with the remaining open items list for
9.0, so it's okay to discuss this during the upcoming reviewfest.

Bernd