MIME-Version: 1.0
References: <CAHewXN=hMbNa3d43NOR=OCgdgpTt18S-1fmueCoEGesyeK4bqw@mail.gmail.com>
 <202403261525.ciahntfgj25o@alvherre.pgsql>
In-Reply-To: <202403261525.ciahntfgj25o@alvherre.pgsql>
From: Tender Wang <tndrwang@gmail.com>
Date: Wed, 27 Mar 2024 16:21:35 +0800
Message-ID: <CAHewXNnHZp0BgLEP2xKoNWCSCBiVxnCkPbz1C+py14n-JsvZHQ@mail.gmail.com>
Subject: Re: Can't find not null constraint, but \d+ shows that
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f9bfa30614a018b4"
Archived-At: <https://www.postgresql.org/message-id/CAHewXNnHZp0BgLEP2xKoNWCSCBiVxnCkPbz1C%2Bpy14n-JsvZHQ%40mail.gmail.com>
Precedence: bulk

--000000000000f9bfa30614a018b4
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Alvaro Herrera <alvherre@alvh.no-ip.org> =E4=BA=8E2024=E5=B9=B43=E6=9C=8826=
=E6=97=A5=E5=91=A8=E4=BA=8C 23:25=E5=86=99=E9=81=93=EF=BC=9A

> On 2024-Mar-26, Tender Wang wrote:
>
> > postgres=3D# CREATE TABLE t1(c0 int, c1 int);
> > postgres=3D# ALTER TABLE  t1 ADD CONSTRAINT Q PRIMARY KEY(c0, c1);
> > postgres=3D# ALTER TABLE  t1 DROP c1;
> >
> > postgres=3D# 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.


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

--000000000000f9bfa30614a018b4
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">Alvaro Herrera &lt;<a href=3D"mailto:=
alvherre@alvh.no-ip.org">alvherre@alvh.no-ip.org</a>&gt; =E4=BA=8E2024=E5=
=B9=B43=E6=9C=8826=E6=97=A5=E5=91=A8=E4=BA=8C 23:25=E5=86=99=E9=81=93=EF=BC=
=9A<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px =
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On 2024-Mar-=
26, Tender Wang wrote:<br>
<br>
&gt; postgres=3D# CREATE TABLE t1(c0 int, c1 int);<br>
&gt; postgres=3D# ALTER TABLE=C2=A0 t1 ADD CONSTRAINT Q PRIMARY KEY(c0, c1)=
;<br>
&gt; postgres=3D# ALTER TABLE=C2=A0 t1 DROP c1;<br>
&gt; <br>
&gt; postgres=3D# ALTER TABLE=C2=A0 t1=C2=A0 ALTER c0 DROP NOT NULL;<br>
&gt; ERROR:=C2=A0 could not find not-null constraint on column &quot;c0&quo=
t;, relation &quot;t1&quot;<br>
<br>
Ooh, hah, what happens here is that we drop the PK constraint<br>
indirectly, so we only go via doDeletion rather than the tablecmds.c<br>
code, so we don&#39;t check the attnotnull flags that the PK was protecting=
.<br>
<br>
&gt; The attached patch is my workaround solution.=C2=A0 Look forward your =
apply.<br>
<br>
Yeah, this is not a very good approach -- I think you&#39;re just guessing<=
br>
that the column is marked NOT NULL because a PK was dropped in the<br>
past -- but really what this catalog state is, is corrupted contents<br>
because the PK drop was mishandled.=C2=A0 At least in theory there are othe=
r<br>
ways to drop a constraint other than dropping one of its columns (for<br>
example, maybe this could happen if you drop a collation that the PK<br>
depends on).=C2=A0 The right approach is to ensure that the PK drop always<=
br>
does the dance that ATExecDropConstraint=C2=A0does.=C2=A0 A good fix probab=
ly just<br>
moves some code from dropconstraint_internal to RemoveConstraintById.<br></=
blockquote><div><br></div><div>I think again, below solutin maybe looks mor=
e better:</div><div>i. move some code from=C2=A0 dropconstraint_internal to=
 RemoveConstraintById,=C2=A0</div><div>=C2=A0 not change the RemoveConstrai=
ntById interface. Ensure that the PK drop always</div><div>=C2=A0 does the =
dance that ATExecDropConstraint does.</div><div><br></div><div>ii. After i =
phase, the attnotnull of some column of primary key=C2=A0 may be reset to f=
alse as I provided example in last email.</div><div>=C2=A0 =C2=A0We can set=
 attnotnull to true again in some place.<br></div></div><br clear=3D"all"><=
div><br></div><span class=3D"gmail_signature_prefix">-- </span><br><div dir=
=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><div>Tender Wang<br></d=
iv>OpenPie:=C2=A0=C2=A0<a href=3D"https://en.openpie.com/" target=3D"_blank=
">https://en.openpie.com/</a></div></div></div>

--000000000000f9bfa30614a018b4--