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

On Thu, Mar 20, 2025 at 8:20 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> On Thu, Mar 20, 2025 at 3:25 PM Rushabh Lathia <rushabh.lathia@gmail.com> wrote:
> >
> > Hi Alvaro,
> >
> > Thank you for the offline discussion.
> >
> > As we all agree, changing the attnotnull datatype would not be a good idea since it is
> > a commonly used catalog column, and many applications and extensions depend on it.
> >
> > Attached is another version of the patch (WIP), where I have introduced a new catalog column,
> > pg_attribute.attinvalidnotnull (boolean). This column will default to FALSE but will be set to TRUE
> > when an INVALID NOT NULL constraint is created.  With this approach, we can avoid performing
> > extra scans on the catalog table to identify INVALID NOT NULL constraints, ensuring there is no
> > performance impact.
> >
> > Also updated the pg_dump implementation patch and attaching the same here.
> >
>
> These patches do not address comments discussed in [1]. Since there
> was a change in design, I am assuming that those will be addressed
> once the design change is accepted.
>
> [1] https://www.postgresql.org/message-id/202503121157.3zabg6m3anwp@alvherre.pgsql

> 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.

main idea:
NOT NULL NOT VALID
* one column one NOT NULL, if you want to change status, it's not
allowed, it will error out, give you hints.
* it will logically be equivalent to CHECK(x IS NOT NULL) NOT VALID.
* it can only be added using ALTER TABLE, not with CREATE TABLE (a
warning will be issued)
* 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.
* an invalid not-null cannot be used while adding a primary key.
* 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.
* partitioned table can not have NOT NULL NOT VALID.