Re: Can't find not null constraint, but \d+ shows that

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> 于2024年3月26日周二 23:25写道：

> On 2024-Mar-26, Tender Wang wrote:
>
> > postgres=# CREATE TABLE t1(c0 int, c1 int);
> > postgres=# ALTER TABLE t1 ADD CONSTRAINT Q PRIMARY KEY(c0, c1);
> > postgres=# ALTER TABLE t1 DROP c1;
> >
> > postgres=# ALTER TABLE t1 ALTER c0 DROP NOT NULL;
> > ERROR: could not find not-null constraint on column "c0", relation "t1"
>
> Ooh, hah, what happens here is that we drop the PK constraint
> indirectly, so we only go via doDeletion rather than the tablecmds.c
> code, so we don't check the attnotnull flags that the PK was protecting.
>
> > The attached patch is my workaround solution. Look forward your apply.
>
> Yeah, this is not a very good approach -- I think you're just guessing
> that the column is marked NOT NULL because a PK was dropped in the
> past -- but really what this catalog state is, is corrupted contents
> because the PK drop was mishandled. At least in theory there are other
> ways to drop a constraint other than dropping one of its columns (for
> example, maybe this could happen if you drop a collation that the PK
> depends on). The right approach is to ensure that the PK drop always
> does the dance that ATExecDropConstraint does. A good fix probably just
> moves some code from dropconstraint_internal to RemoveConstraintById.
>

I think again, below solutin maybe looks more better:
i. move some code from dropconstraint_internal to RemoveConstraintById,
not change the RemoveConstraintById interface. Ensure that the PK drop
always
does the dance that ATExecDropConstraint does.

ii. After i phase, the attnotnull of some column of primary key may be
reset to false as I provided example in last email.
We can set attnotnull to true again in some place.

--
Tender Wang
OpenPie: https://en.openpie.com/