From: Chao Li <li.evan.chao@gmail.com>
Message-Id: <2B70C6CA-606B-438C-8EF2-84DCBA1C0813@gmail.com>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_47783623-44BC-44C3-B8DD-46CB34E7C027"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\))
Subject: Re: let ALTER TABLE DROP COLUMN drop whole-row referenced object
Date: Thu, 11 Sep 2025 09:27:27 +0800
In-Reply-To: 
 <CACJufxFMqpraCreGKTWWB7cyPAYP+yehwTMFm-CSx+rzWdHf+A@mail.gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
To: jian he <jian.universality@gmail.com>
References: 
 <CACJufxGA6KVQy7DbHGLVw9s9KKmpGyZt5ME6C7kEfjDpr2wZCw@mail.gmail.com>
 <CACJufxFMqpraCreGKTWWB7cyPAYP+yehwTMFm-CSx+rzWdHf+A@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/2B70C6CA-606B-438C-8EF2-84DCBA1C0813%40gmail.com>
Precedence: bulk


--Apple-Mail=_47783623-44BC-44C3-B8DD-46CB34E7C027
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8


> On Sep 9, 2025, at 11:12, jian he <jian.universality@gmail.com> wrote:
>=20
> hi.
> I found a new way to solve this problem.
>=20
> CREATE TABLE ts (a int, c int, b int
>    constraint cc check((ts =3D ROW(1,1,1))),
>    constraint cc1 check((ts.a =3D 1)));
>=20
>=20
> ALTER TABLE ts DROP COLUMN a CASCADE;
> will drop above all indexes, constraints and policies on the table ts.
> =
<v2-0001-ALTER-TABLE-DROP-COLUMN-drop-wholerow-referenced-object.patch>


I agree we should delete those constraints and indices on the whole row, =
otherwise, with cc (ts=3DROW(1,1,1)), once a column is dropped, it =
won=E2=80=99t be able to insert data anymore:

```
evantest=3D# insert into ts values (2, 3);
ERROR:  new row for relation "ts" violates check constraint "cc"
DETAIL:  Failing row contains (2, 3).
evantest=3D# insert into ts values (1, 1);
ERROR:  cannot compare record types with different numbers of columns
```

But v2 needs a rebase, I cannot apply it to master.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/


--Apple-Mail=_47783623-44BC-44C3-B8DD-46CB34E7C027
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; =
charset=3Dutf-8"></head><body style=3D"overflow-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;"><br =
id=3D"lineBreakAtBeginningOfMessage"><div><br><blockquote =
type=3D"cite"><div>On Sep 9, 2025, at 11:12, jian he =
&lt;jian.universality@gmail.com&gt; wrote:</div><br =
class=3D"Apple-interchange-newline"><div><div>hi.<br>I found a new way =
to solve this problem.<br><br>CREATE TABLE ts (a int, c int, b int<br> =
&nbsp;&nbsp;&nbsp;constraint cc check((ts =3D ROW(1,1,1))),<br> =
&nbsp;&nbsp;&nbsp;constraint cc1 check((ts.a =3D 1)));<br><br><br>ALTER =
TABLE ts DROP COLUMN a CASCADE;<br>will drop above all indexes, =
constraints and policies on the table ts.<br><span =
id=3D"cid:f_mfbz62ro0">&lt;v2-0001-ALTER-TABLE-DROP-COLUMN-drop-wholerow-r=
eferenced-object.patch&gt;</span></div></div></blockquote></div><div><br><=
/div><div>I agree we should delete those constraints and indices on the =
whole row, otherwise, with cc (ts=3DROW(1,1,1)), once a column is =
dropped, it won=E2=80=99t be able to insert data =
anymore:</div><div><br></div><div>```</div><div><div>evantest=3D# insert =
into ts values (2, 3);</div><div>ERROR: &nbsp;new row for relation "ts" =
violates check constraint "cc"</div><div>DETAIL: &nbsp;Failing row =
contains (2, 3).</div><div>evantest=3D# insert into ts values (1, =
1);</div><div>ERROR: &nbsp;cannot compare record types with different =
numbers of columns</div></div><div>```</div><div><br></div><div>But v2 =
needs a rebase, I cannot apply it to master.</div><div><br></div>Best =
regards,<br><div>
<div dir=3D"auto" style=3D"caret-color: rgb(0, 0, 0); color: rgb(0, 0, =
0); letter-spacing: normal; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; word-spacing: 0px; =
-webkit-text-stroke-width: 0px; text-decoration: none; overflow-wrap: =
break-word; -webkit-nbsp-mode: space; line-break: =
after-white-space;"><div>--</div><div>Chao Li (Evan)<br>HighGo Software =
Co., Ltd.<br>https://www.highgo.com/</div><div><br></div></div><br =
class=3D"Apple-interchange-newline"><br =
class=3D"Apple-interchange-newline">

</div>
<br></body></html>=

--Apple-Mail=_47783623-44BC-44C3-B8DD-46CB34E7C027--