Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

On 2025-Mar-12, Ashutosh Bapat wrote:

> The 002_pg_upgrade test passes with and without my patches now. But
> then the tests added here do not leave behind any parent-child table.
> Previously we have found problems in dumping and restoring constraints
> in an inheritance hierarchy. I think the test should leave behind all
> the combinations of parent and child NOT NULL constraints so that
> 0002_pg_upgrade can test those.

I agree.

> Is it expected that a child may have VALID constraint but parent has
> not valid constraint?

Sure. Consider: if the parent has an unvalidated constraint, we cannot
make any assertions about the state of its children. The children may
have additional constraints of their own -- in this case, a child can
have a validated constraint even though the parent has none, or only an
unvalidatec constraint.

But the opposite is not allowed: if you know something to be true about
a parent table (to wit: that no row in it is NULL), then this must
necessarily apply to its children as well. Therefore, if there's a
valid constraint in the parent, then all children must have the same
constraint, and all such constraints must be known valid.

> Same case with partitioned table. We should leave partitioned table
> hierarchy behind for 002_pg_upgrade to test. And we need tests to test
> scenarios where a partitioned table has valid constraint but we try to
> change constraint on a partition to not valid and vice versa. I think
> we shouldn't allow such assymetry in partitioned table hierarchy and
> having a test would be better.

Agreed.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/