MIME-Version: 1.0
References: <202404101358.iet3euazs5zf@alvherre.pgsql> <202404101723.4ag5z2mbgmsl@alvherre.pgsql>
 <CACJufxHX9d1-beCC3HaR7_DXpfjb0j2ybRJ_Av5q41N2wido_A@mail.gmail.com> <CAHewXNk5m95hu8MSmKX4QFb2UK+uuARm4W8PtQs-unSt=XiaTA@mail.gmail.com>
In-Reply-To: <CAHewXNk5m95hu8MSmKX4QFb2UK+uuARm4W8PtQs-unSt=XiaTA@mail.gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Thu, 11 Apr 2024 16:49:35 +0800
Message-ID: <CACJufxFfcqScDSXTfM8BKMGbNdy1v0fcrEZHRR5BejtOTceG+A@mail.gmail.com>
Subject: Re: Can't find not null constraint, but \d+ shows that
To: Tender Wang <tndrwang@gmail.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CACJufxFfcqScDSXTfM8BKMGbNdy1v0fcrEZHRR5BejtOTceG%2BA%40mail.gmail.com>
Precedence: bulk

On Thu, Apr 11, 2024 at 3:19=E2=80=AFPM Tender Wang <tndrwang@gmail.com> wr=
ote:
>
>> +DROP TABLE notnull_tbl1;
>> +-- make sure attnotnull is reset correctly when a PK is dropped indirec=
tly
>> +CREATE TABLE notnull_tbl1 (c0 int, c1 int, PRIMARY KEY (c0, c1));
>> +ALTER TABLE  notnull_tbl1 DROP c1;
>> +\d+ notnull_tbl1
>> +                               Table "public.notnull_tbl1"
>> + Column |  Type   | Collation | Nullable | Default | Storage | Stats
>> target | Description
>> +--------+---------+-----------+----------+---------+---------+---------=
-----+-------------
>> + c0     | integer |           | not null |         | plain   |         =
     |
>> +
>>
>> this is not what we expected?
>> "not null" for "c0" now should be false?
>> am I missing something?
>
> Yeah, now this is expected behavior.
> Users can  drop manually not-null of "c0" if they want, and no error repo=
rte.
>

sorry for the noise.
these two past patches confused me:
0001-Correctly-reset-attnotnull-when-constraints-dropped-.patch
v4-0001-Fix-pg_attribute-attnotnull-not-reset-when-droppe.patch

I thought dropping a column of primary key (ALTER TABLE notnull_tbl2 DROP c=
1)
will make the others key columns to not have "not null" property.

now I figured out that
dropping a column of primary key columns will not change other key
columns'  "not null" property.
dropping the primary key associated constraint will make all key
columns "not null" property disappear.

v2-0001-Handle-ALTER-.-DROP-NOT-NULL-when-no-pg_constrain.patch
behavior looks fine to me now.


inline drop_orphaned_notnull in ATExecDropNotNull looks fine to me.