jian he <jian.universal...@gmail.com> 于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/