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

On 2025-Apr-26, jian he wrote:

> I am wondering if we need to change the following comments in getTableAttrs.
>
> * We track in notnull_islocal whether the constraint was defined directly
> * in this table or via an ancestor, for binary upgrade. flagInhAttrs
> * might modify this later for servers older than 18; it's also in charge
> * of determining the correct inhcount.
> since we also changed notnull_islocal for pg18.

Yeah.

> Also do we need to adjust the following comments in determineNotNullFlags?
> * In a child table that inherits from a parent already containing NOT NULL
> * constraints and the columns in the child don't have their own NOT NULL
> * declarations, we suppress printing constraints in the child: the
> * constraints are acquired at the point where the child is attached to the
> * parent. This is tracked in ->notnull_islocal (which is set in flagInhAttrs
> * for servers pre-18).

Adjusted this one as well.

I also fixed the business with multiple inheritance: with the commit I
just pushed, we stop printing the child constraint if at least one
parent has a validated constraint.

With that, I believe this open item is closed, so I'm going to mark it
as such in the wiki page momentarily.

> > Patch 0002 is a part of your proposed patch that I don't think we need,
> > but I'm open to hearing arguments about why we do, preferrably with some
> > test cases.
>
> ------------
> CREATE TABLE inhnn (a int);
> insert into inhnn values(NULL);
> ALTER TABLE inhnn ADD CONSTRAINT cc not null a NOT VALID;
> CREATE TABLE inhnn_cc(a INTEGER) INHERITS(inhnn);
> CREATE TABLE inhnn_cc_1(a INTEGER) INHERITS(inhnn_cc, inhnn);
> --------

> As you can see, "conislocal" is not consistent, maybe in practical it
> does not matter,
> but here we can make pg_dump, "conislocal" value being consistent.

Yeah, I realize that this effect exists, but I find it very hard to
justify spending time on this issue given that there are no visible
consequences. What I can offer is that if you come up with a test setup
that fails when this patch is not applied, and works when it is applied,
then I'm open to considering it.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
Syntax error: function hell() needs an argument.
Please choose what hell you want to involve.