Feedback-ID: ia2694551:Fastmail
Date: Thu, 20 Mar 2025 16:52:57 +0100
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: jian he <jian.universality@gmail.com>
Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>,
	Rushabh Lathia <rushabh.lathia@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Support NOT VALID / VALIDATE constraint options for named NOT
 NULL constraints
Message-ID: <202503201552.civdhzzi5zc6@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CACJufxG0A-_ppTBzGT=L99AFv2XF+xbx19H7XaQTQ_rZGYcj-w@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/202503201552.civdhzzi5zc6%40alvherre.pgsql>
Precedence: bulk

On 2025-Mar-20, jian he wrote:

> > Is it expected that a child may have VALID constraint but parent has
> > not valid constraint?
>
> but the MergeConstraintsIntoExisting logic is when
> ALTER TABLE ATTACH PARTITION,
> it expects the child table to also have an equivalent constraint
> definition on it.
> see MergeConstraintsIntoExisting:
>             ereport(ERROR,
>                     (errcode(ERRCODE_DATATYPE_MISMATCH),
>                      errmsg("child table is missing constraint \"%s\"",
>                             NameStr(parent_con->conname))));
> 
> So I decided not to support it.

> * partitioned table can not have NOT NULL NOT VALID.

I'm not sure I understand what you're saying here.  I think a
partitioned table can be allowed to have a NOT VALID constraint.  BUT if
it does, then all the children must have a corresponding constraint.  The
constraint on children may be valid or may be invalid; the parent
doesn't force the issue one way or the other.  But it has to exist.
Also, if you run ALTER TABLE VALIDATE CONSTRAINT on the parent, then at
that point you have to validate that all those corresponding constraints on
the children are also validated.

> * one column one NOT NULL, if you want to change status, it's not
> allowed, it will error out, give you hints.

I think we discussed this already.  If you say 
ALTER TABLE .. ALTER COLUMN .. SET NOT NULL
and an invalid constraint exists, then we can simply validate that
constraint.

However, if you say
ALTER TABLE .. ADD CONSTRAINT foobar NOT NULL col;
and an invalid constraint exists whose name is different from foobar,
then we should raise an error, because the user's requirement that the
constraint is named foobar cannot be satisfied.  If the constraint is
named foobar, OR if the user doesn't specify a constraint name
  ALTER TABLE .. ADD NOT NULL col;
then it's okay to validate that constraint without raising an error.
The important thing being that the user requirement is satisfied.

> * it can only be added using ALTER TABLE, not with CREATE TABLE (a
> warning will be issued)

I think the issue of adding constraints with NOT VALID during CREATE
TABLE is the topic of another thread.  We already silently ignore the
NOT VALID markers during CREATE TABLE for other types of constraints.

> * pg_attribute.attinvalidnotnull meaning: this attnum has a
> (convalidated == false) NOT NULL pg_constraint entry to it.
> * if attnotnull is true, then attinvalidnotnull should be false.
>   Conversely, if attinvalidnotnull is true, then attnotnull should be false.

I don't like this.  It seems baroque and it will break existing
applications, because they currently query for attnotnull and assume
that inserting a null value will work, but in reality it will fail
because attinvalidnotnull is true (meaning an invalid constraint exists,
which prevents inserting nulls).

I think the idea should be: attnotnull means that a constraint exists;
it doesn't imply anything regarding the constraint being valid or not.
attnotnullvalid will indicate whether the constraint is valid; this
column can only be true if attnotnull is already true.

> * an invalid not-null cannot be used while adding a primary key.

Check.

> * if attinvalidnotnull is true, this column can not accept NULL values,
>   but the existing column value may contain NULLs, we need to
>   VALIDATE the not-null constraint to check if this column exists NULL
> values or not.

Check.


-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/