Re: Can't find not null constraint, but \d+ shows that - Mailing list pgsql-hackers

From jian he
Subject Re: Can't find not null constraint, but \d+ shows that
Date
Msg-id CACJufxE9Oaj7okHemh6j33h9oWns_FQhqKp5MpSKEWNgH7zfrw@mail.gmail.com
Whole thread Raw
In response to Re: Can't find not null constraint, but \d+ shows that  (Alvaro Herrera <alvherre@alvh.no-ip.org>)
Responses Re: Can't find not null constraint, but \d+ shows that
List pgsql-hackers
On Fri, Apr 12, 2024 at 3:52 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>
> On 2024-Apr-12, jian he wrote:
>
> > Now I am more confused...
>
> > +CREATE TABLE notnull_tbl1 (c0 int, c1 int, PRIMARY KEY (c0, c1));
> > +ALTER TABLE  notnull_tbl1 DROP c1;
>
> > same query, mysql make let "c0" be not null
>
> Yes, that was Postgres' old model.  But the way we think of it now, is
> that a column is marked attnotnull when a pg_constraint entry exists to
> support that flag, which can be a not-null constraint, or a primary key
> constraint.  In the old Postgres model, you're right that we would
> continue to have c0 as not-null, just like mysql.  In the new model,
> that flag no longer has no reason to be there, because the backing
> primary key constraint has been removed, which is why we reset it.
>
> So what I was saying in the cases with replica identity and generated
> columns, is that there's an attnotnull flag we cannot remove, because of
> either of those things, but we don't have any backing constraint for it,
> which is an inconsistency with the view of the world that I described
> above.  I would like to manufacture one not-null constraint at that
> point, or just abort the drop of the PK ... but I don't see how to do
> either of those things.
>

thanks for your explanation.
now I understand it.
I wonder is there any incompatibility issue, or do we need to say something
about the new behavior when dropping a key column?

the comments look good to me.

only minor cosmetic issue:
+ if (unconstrained_cols)
i would like change it to
+ if (unconstrained_cols != NIL)

+ foreach(lc, unconstrained_cols)
we can change to
+ foreach_int(attnum, unconstrained_cols)
per commit
https://git.postgresql.org/cgit/postgresql.git/commit/?id=14dd0f27d7cd56ffae9ecdbe324965073d01a9ff



pgsql-hackers by date:

Previous
From: Michael Paquier
Date:
Subject: Re: Simplify documentation related to Windows builds
Next
From: Andres Freund
Date:
Subject: [MASSMAIL]ci: Allow running mingw tests by default via environment variable