Re: Prevent ALTER TABLE DROP NOT NULL on child tables if parent column has it

Michael Paquier <michael(dot)paquier(at)gmail(dot)com> writes:
> To put it short, it should not be possible to drop a NOT NULL
> constraint on a child relation when its parent table is using it, this
> should only be possible from the parent. Attached is a patch handling
> this problem by adding a new function in pg_inherits.c to fetch the
> list of parent relations for a given relation OID, and did some
> refactoring to stick with what is done when scanning child relations.

This doesn't sound like the right approach; in particular, it won't really
help for deciding whether to propagate a DROP NOT NULL on a parent rel to
its children. What we've discussed in the past is to store NOT NULL
constraints in pg_constraint, much like CHECK constraints are already, and
use use-count logic identical to the CHECK case to keep track of whether
NOT NULL constraints are inherited or not. My feeling is that we'd keep
the pg_attribute.attnotnull field and continue to drive actual enforcement
off that, but it would just reflect a summary of the pg_constraint state.

IIRC, Alvaro posted a WIP patch for that awhile back. Not sure what the
current state is.

regards, tom lane