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

On 2025-Mar-24, jian he wrote:

> hi.
> you may like the attached. it's based on your idea: attnotnullvalid.

This is quite close to what I was thinking, yeah.  I noticed a couple of
bugs however, and ended up cleaning up the whole thing.  Here's what I
have so far.  I'm not sure the pg_dump bits are okay (apart from what
you report below) -- I think it's losing the constraint names, which is
of course unacceptable.

I think the warnings about creating constraints as valid when
originating as invalid are unnecessary at this point.  We should add
those, or not, for all constraint types, not just not-null.  That's IMO
a separate discussion.

> I came across a case, not sure if it's a bug.
> CREATE TABLE ttchk (a INTEGER);
> ALTER TABLE ttchk ADD CONSTRAINT cc check (a is NOT NULL) NOT VALID;
> CREATE TABLE ttchk_child(a INTEGER) INHERITS(ttchk);
> ttchk_child's constraint cc will default to valid,
> but pg_dump && pg_restore will make ttchk_child's constraint invalid.
> since it's an existing behavior, so not-null constraint will align with it.

Hmm, yes, such pg_dump behavior would be incorrect.  I'll give the
pg_dump code another look tomorrow.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/