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: Wed, 10 Apr 2024 18:11:02 +0800
Message-ID: <CAHewXN=M-UcaDgX0N-SbS+h2fAMN6NR3yxZSynSLn5FETBS4sA@mail.gmail.com> (raw)
In-Reply-To: <CACJufxEfc0obH8Aw2AExudy1g5s08e1aFgEJ_KbeZw0NhYeSOw@mail.gmail.com>
References: <CAHewXNmLog6Apch1TsS6cEpeQVJjG44ciNN6NzmPJmtKsnvoiQ@mail.gmail.com>
<[email protected]>
<CACJufxEOzyt+NmbaoABY0pskX73ps=LFdKNDNUSiniSHBfY5Hg@mail.gmail.com>
<CAHewXNnUCfQme3YYe4CM1fY7+kMKGJzbdsWB5oSQYnUM6k+xPw@mail.gmail.com>
<CACJufxEfc0obH8Aw2AExudy1g5s08e1aFgEJ_KbeZw0NhYeSOw@mail.gmail.com>
jian he <[email protected]> 于2024年4月10日周三 17:34写道：
>
> another related bug, in master.
>
> drop table if exists notnull_tbl1;
> CREATE TABLE notnull_tbl1 (c0 int not null, c1 int);
> ALTER TABLE notnull_tbl1 ADD CONSTRAINT Q PRIMARY KEY(c0, c1);
> \d+ notnull_tbl1
> ALTER TABLE notnull_tbl1 ALTER c0 DROP NOT NULL;
> ALTER TABLE notnull_tbl1 ALTER c1 DROP NOT NULL;
>
> "ALTER TABLE notnull_tbl1 ALTER c0 DROP NOT NULL;"
> should fail?
>
Yeah, it should fail as before, because c0 is primary key.
In master, although c0's pg_attribute.attnotnull is still true, but its
not-null constraint has been deleted
in dropconstraint_internal().
If we drop column c1 after dropping c0 not null, the primary key will be
dropped indirectly.
And now you can see c0 is still not-null if you do \d+ notnull_tbl1. But it
will report error "not found not-null"
if you alter c0 drop not null.
postgres=# ALTER TABLE notnull_tbl1 ALTER c0 DROP NOT NULL;
ALTER TABLE
postgres=# \d+ notnull_tbl1
Table "public.notnull_tbl1"
Column | Type | Collation | Nullable | Default | Storage | Compression
| Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
c0 | integer | | not null | | plain |
| |
c1 | integer | | not null | | plain |
| |
Indexes:
"q" PRIMARY KEY, btree (c0, c1)
Access method: heap
postgres=# alter table notnull_tbl1 drop c1;
ALTER TABLE
postgres=# \d notnull_tbl1
Table "public.notnull_tbl1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
c0 | integer | | not null |
postgres=# alter table notnull_tbl1 alter c0 drop not null;
ERROR: could not find not-null constraint on column "c0", relation
"notnull_tbl1"
--
Tender Wang
OpenPie: https://en.openpie.com/
view thread (4+ messages)
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: <CAHewXN=M-UcaDgX0N-SbS+h2fAMN6NR3yxZSynSLn5FETBS4sA@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