MIME-Version: 1.0
References: <202404101358.iet3euazs5zf@alvherre.pgsql> <202404101723.4ag5z2mbgmsl@alvherre.pgsql>
In-Reply-To: <202404101723.4ag5z2mbgmsl@alvherre.pgsql>
From: jian he <jian.universality@gmail.com>
Date: Thu, 11 Apr 2024 14:40:04 +0800
Message-ID: <CACJufxHX9d1-beCC3HaR7_DXpfjb0j2ybRJ_Av5q41N2wido_A@mail.gmail.com>
Subject: Re: Can't find not null constraint, but \d+ shows that
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Tender Wang <tndrwang@gmail.com>, 
	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/CACJufxHX9d1-beCC3HaR7_DXpfjb0j2ybRJ_Av5q41N2wido_A%40mail.gmail.com>
Precedence: bulk

On Wed, Apr 10, 2024 at 2:10=E2=80=AFPM jian he <jian.universality@gmail.co=
m> wrote:
>
> DROP TABLE if exists notnull_tbl2;
> CREATE TABLE notnull_tbl2 (c0 int generated by default as IDENTITY, c1 in=
t);
> ALTER TABLE notnull_tbl2 ADD CONSTRAINT Q PRIMARY KEY(c0, c1);
> ALTER TABLE notnull_tbl2 DROP CONSTRAINT notnull_tbl2_c0_not_null;
> ALTER TABLE notnull_tbl2 DROP c1;
> \d notnull_tbl2

> ALTER TABLE notnull_tbl2 DROP CONSTRAINT notnull_tbl2_c0_not_null;
per above sequence execution order, this should error out?

otherwise which "not null" (attribute|constraint) to anchor "generated
by default as identity" not null property?
"DROP c1" will drop the not null property for "c0" and "c1".
if "DROP CONSTRAINT notnull_tbl2_c0_not_nul" not error out, then
" ALTER TABLE notnull_tbl2 DROP c1;"
should either error out
or transform "c0" from "c0 int generated by default as identity"
to
"c0 int"


On Thu, Apr 11, 2024 at 1:23=E2=80=AFAM Alvaro Herrera <alvherre@alvh.no-ip=
.org> wrote:
>
> On 2024-Apr-10, Alvaro Herrera wrote:
>
> > One thing missing here is pg_dump support.  If you just dump this table=
,
> > it'll end up with no constraint at all.  That's obviously bad, so I
> > propose we have pg_dump add a regular NOT NULL constraint for those, to
> > avoid perpetuating the weird situation further.
>
> Here's another crude patchset, this time including the pg_dump aspect.
>

+DROP TABLE notnull_tbl1;
+-- make sure attnotnull is reset correctly when a PK is dropped indirectly
+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?