public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tender Wang <[email protected]>
To: jian he <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Can't find not null constraint, but \d+ shows that
Date: Thu, 11 Apr 2024 15:19:35 +0800
Message-ID: <CAHewXNk5m95hu8MSmKX4QFb2UK+uuARm4W8PtQs-unSt=XiaTA@mail.gmail.com> (raw)
In-Reply-To: <CACJufxHX9d1-beCC3HaR7_DXpfjb0j2ybRJ_Av5q41N2wido_A@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CACJufxHX9d1-beCC3HaR7_DXpfjb0j2ybRJ_Av5q41N2wido_A@mail.gmail.com>
jian he <[email protected]> 于2024年4月11日周四 14:40写道：
> On Wed, Apr 10, 2024 at 2:10 PM jian he <[email protected]>
> wrote:
> >
> > DROP TABLE if exists notnull_tbl2;
> > CREATE TABLE notnull_tbl2 (c0 int generated by default as IDENTITY, c1
> int);
> > 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"
>
> I try above case on MASTER and MASTER with Alvaro V2 patch, and all work
correctly.
\d+ notnull_tbl2 will see not-null of "c0".
>
> On Thu, Apr 11, 2024 at 1:23 AM Alvaro Herrera <[email protected]>
> 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?
>
Yeah, now this is expected behavior.
Users can drop manually not-null of "c0" if they want, and no error
reporte.
--
Tender Wang
OpenPie: https://en.openpie.com/
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Can't find not null constraint, but \d+ shows that
In-Reply-To: <CAHewXNk5m95hu8MSmKX4QFb2UK+uuARm4W8PtQs-unSt=XiaTA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox