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

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.

Thanks,


On Mon, Mar 17, 2025 at 3:23 PM Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:

> On 2025-Mar-17, jian he wrote:
>
> > hi.
> > I played around with it.
> >
> > current syntax, we don't need to deal with column constraint grammar.
> > like the following can fail directly:
> > create table t0(a int constraint nn not null a not valid);
> > we only support table constraint cases like:
> > alter table lp add constraint nc1 not null a not valid;
> >
> > since CREATE TABLE with invalid constraint does not make sense, so
> > we can just issue a warning. like:
> > create table t0(a int, constraint nn not null a not valid);
> > WARNING:  CREATE TABLE NOT NULL NOT VALID CONSTRAINT WILL SET TO VALID
> > the wording needs to change...
>
> Yeah, we discussed this elsewhere.  I have an alpha-quality patch for
> that, but I wasn't too sure about it ...
>
> [1]
> https://postgr.es/m/CACJufxEQcHNhN6M18JY1mQcgQq9Gn9ofMeop47SdFDE5B8wbug@mail.gmail.com
>
> --
> Álvaro Herrera               48°01'N 7°57'E  —
> https://www.EnterpriseDB.com/
>


-- 
Rushabh Lathia