From: Chao Li <li.evan.chao@gmail.com>
Content-Type: multipart/mixed;
	boundary="Apple-Mail=_3E7818AA-ED84-4BC4-BC4D-420C6E508E3A"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.600.51.1.1\))
Subject: Fix SET EXPRESSION for virtual columns with whole-row dependencies
Message-Id: <71242FEA-15A3-4073-9FA1-561CA744ACBD@gmail.com>
Date: Tue, 9 Jun 2026 14:22:32 +0800
Cc: Peter Eisentraut <peter@eisentraut.org>,
 jian he <jian.universality@gmail.com>
To: Postgres hackers <pgsql-hackers@lists.postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/71242FEA-15A3-4073-9FA1-561CA744ACBD%40gmail.com>
Precedence: bulk


--Apple-Mail=_3E7818AA-ED84-4BC4-BC4D-420C6E508E3A
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

Hi,

While testing =E2=80=9C[f80bedd52] Allow ALTER COLUMN SET EXPRESSION on =
virtual columns=E2=80=9D, I found that the feature missed handling =
whole-row check constraints.

Here is a repro:
```
evantest=3D# create table t(
evantest(#   a int,
evantest(#   b int generated always as (a*2) virtual,
evantest(#   constraint row_c check (t is not null)
evantest(# );
CREATE TABLE
evantest=3D# insert into t(a) values(1);
INSERT 0 1
evantest=3D# alter table t alter b set expression as (nullif(a, 1));
ALTER TABLE
evantest=3D# select * from t;
 a | b
---+---
 1 |
(1 row)
```

The ALTER TABLE should fail, because it makes b become NULL, which =
breaks the constraint row_c.

For comparison, if we use the nullif expression at table creation time, =
we cannot insert a row with a =3D 1:
```
evantest=3D# create table t1(
evantest(#   a int,
evantest(#   b int generated always as (nullif(a,1)) virtual,
evantest(#   constraint row_c check (t1 is not null)
evantest(# );
CREATE TABLE
evantest=3D# insert into t1(a) values(1);
ERROR:  new row for relation "t1" violates check constraint "row_c"
DETAIL:  Failing row contains (1, virtual).
```

I think the problem is that ATExecSetExpression() only calls =
RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, =
colName); to find dependent objects. In this repro, attnum is 2, which =
is b=E2=80=99s attnum, and colName is b, so it doesn=E2=80=99t find the =
whole-row constraint row_c. The same problem applies to whole-row =
indexes as well.

Since RememberAllDependentForRebuilding() is only used by =
AT_AlterColumnType and AT_SetExpression, I enhanced it to handle attnum =
=3D=3D 0 for AT_SetExpression, so that whole-row dependent constraints =
and indexes are remembered for a virtual generated column.

See the attached patch for details. With the fix, rerunning the repro =
makes ALTER TABLE SET EXPRESSION fail as expected:
```
evantest=3D# alter table t alter b set expression as (nullif(a, 1));
ERROR:  check constraint "row_c" of relation "t" is violated by some row
```

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


--Apple-Mail=_3E7818AA-ED84-4BC4-BC4D-420C6E508E3A
Content-Disposition: attachment;
	filename=v1-0001-Fix-SET-EXPRESSION-with-whole-row-virtual-column-.patch
Content-Type: application/octet-stream;
	x-unix-mode=0644;
	name="v1-0001-Fix-SET-EXPRESSION-with-whole-row-virtual-column-.patch"
Content-Transfer-Encoding: quoted-printable

=46rom=2054088e2c329dfed17e861cbe8d397e5b707485ed=20Mon=20Sep=2017=20=
00:00:00=202001=0AFrom:=20"Chao=20Li=20(Evan)"=20<lic@highgo.com>=0A=
Date:=20Tue,=209=20Jun=202026=2014:05:00=20+0800=0ASubject:=20[PATCH=20=
v1]=20Fix=20SET=20EXPRESSION=20with=20whole-row=20virtual=20column=0A=20=
dependencies=0A=0AALTER=20COLUMN=20SET=20EXPRESSION=20on=20a=20virtual=20=
generated=20column=20only=20looked=20for=0Adependencies=20on=20the=20=
column's=20attnum.=20=20Whole-row=20expressions,=20such=20as=0A"rel=20IS=20=
NOT=20NULL",=20depend=20on=20the=20relation=20as=20a=20whole=20instead,=20=
but=20they=20can=0Astill=20observe=20the=20virtual=20column's=20computed=20=
value.=0A=0ATeach=20the=20SET=20EXPRESSION=20path=20to=20also=20scan=20=
whole-relation=20dependencies=20for=0Avirtual=20generated=20columns.=20=20=
This=20lets=20whole-row=20CHECK=20constraints=20be=0Arevalidated=20and=20=
whole-row=20expression=20indexes=20be=20rebuilt=20after=20the=20=
expression=0Achanges.=0A=0AAdd=20regression=20coverage=20for=20a=20=
whole-row=20CHECK=20constraint,=20a=20whole-row=0Aexpression=20index,=20=
and=20an=20ordinary=20index=20that=20should=20not=20be=20rebuilt.=0A=0A=
Author:=20Chao=20Li=20<lic@highgo.com>=0A---=0A=20=
src/backend/commands/tablecmds.c=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
|=2040=20+++++++++++++++++--=0A=20=
.../regress/expected/generated_virtual.out=20=20=20=20|=2019=20+++++++++=0A=
=20src/test/regress/sql/generated_virtual.sql=20=20=20=20|=20=208=20++++=0A=
=203=20files=20changed,=2064=20insertions(+),=203=20deletions(-)=0A=0A=
diff=20--git=20a/src/backend/commands/tablecmds.c=20=
b/src/backend/commands/tablecmds.c=0Aindex=20a33e22e8e61..594aabfa295=20=
100644=0A---=20a/src/backend/commands/tablecmds.c=0A+++=20=
b/src/backend/commands/tablecmds.c=0A@@=20-8765,6=20+8765,15=20@@=20=
ATExecSetExpression(AlteredTableInfo=20*tab,=20Relation=20rel,=20const=20=
char=20*colName,=0A=20=09=20*=20and=20record=20enough=20information=20to=20=
let=20us=20recreate=20the=20objects.=0A=20=09=20*/=0A=20=09=
RememberAllDependentForRebuilding(tab,=20AT_SetExpression,=20rel,=20=
attnum,=20colName);=0A+=09if=20(attgenerated=20=3D=3D=20=
ATTRIBUTE_GENERATED_VIRTUAL)=0A+=09{=0A+=09=09/*=0A+=09=09=20*=20=
Whole-row=20expressions,=20such=20as=20"rel=20IS=20NOT=20NULL",=20depend=20=
on=20the=0A+=09=09=20*=20relation=20as=20a=20whole=20rather=20than=20on=20=
the=20virtual=20generated=20column's=0A+=09=09=20*=20attnum,=20but=20=
they=20can=20observe=20the=20column's=20new=20computed=20value.=0A+=09=09=
=20*/=0A+=09=09RememberAllDependentForRebuilding(tab,=20=
AT_SetExpression,=20rel,=200,=20NULL);=0A+=09}=0A=20=0A=20=09/*=0A=20=09=20=
*=20Drop=20the=20dependency=20records=20of=20the=20GENERATED=20=
expression,=20in=20particular=0A@@=20-15293,7=20+15302,9=20@@=20=
ATExecAlterColumnType(AlteredTableInfo=20*tab,=20Relation=20rel,=0A=20/*=0A=
=20=20*=20Subroutine=20for=20ATExecAlterColumnType=20and=20=
ATExecSetExpression:=20Find=20everything=0A=20=20*=20that=20depends=20on=20=
the=20column=20(constraints,=20indexes,=20etc),=20and=20record=20enough=0A=
-=20*=20information=20to=20let=20us=20recreate=20the=20objects.=0A+=20*=20=
information=20to=20let=20us=20recreate=20the=20objects.=20=20For=20=
virtual=20generated=20columns,=0A+=20*=20ATExecSetExpression=20can=20=
also=20pass=20attnum=20=3D=3D=200=20to=20find=20whole-row=20expression=0A=
+=20*=20dependencies=20that=20observe=20the=20column's=20computed=20=
value.=0A=20=20*/=0A=20static=20void=0A=20=
RememberAllDependentForRebuilding(AlteredTableInfo=20*tab,=20=
AlterTableType=20subtype,=0A@@=20-15305,6=20+15316,7=20@@=20=
RememberAllDependentForRebuilding(AlteredTableInfo=20*tab,=20=
AlterTableType=20subtype,=0A=20=09HeapTuple=09depTup;=0A=20=0A=20=09=
Assert(subtype=20=3D=3D=20AT_AlterColumnType=20||=20subtype=20=3D=3D=20=
AT_SetExpression);=0A+=09Assert(attnum=20!=3D=200=20||=20subtype=20=3D=3D=20=
AT_SetExpression);=0A=20=0A=20=09depRel=20=3D=20=
table_open(DependRelationId,=20RowExclusiveLock);=0A=20=0A@@=20-15355,6=20=
+15367,15=20@@=20RememberAllDependentForRebuilding(AlteredTableInfo=20=
*tab,=20AlterTableType=20subtype,=0A=20=09=09=09=09=09}=0A=20=09=09=09=09=
=09else=0A=20=09=09=09=09=09{=0A+=09=09=09=09=09=09/*=0A+=09=09=09=09=09=09=
=20*=20In=20a=20whole-relation=20scan=20for=20SET=20EXPRESSION,=20ignore=0A=
+=09=09=09=09=09=09=20*=20relation=20dependencies=20that=20are=20not=20=
indexes.=20=20Objects=0A+=09=09=09=09=09=09=20*=20with=20expressions=20=
that=20need=20action,=20such=20as=0A+=09=09=09=09=09=09=20*=20=
constraints,=20are=20handled=20by=20their=20own=20cases.=0A+=09=09=09=09=09=
=09=20*/=0A+=09=09=09=09=09=09if=20(attnum=20=3D=3D=200)=0A+=09=09=09=09=09=
=09=09break;=0A+=0A=20=09=09=09=09=09=09/*=20Not=20expecting=20any=20=
other=20direct=20dependencies...=20*/=0A=20=09=09=09=09=09=09elog(ERROR,=20=
"unexpected=20object=20depending=20on=20column:=20%s",=0A=20=09=09=09=09=09=
=09=09=20getObjectDescription(&foundObject,=20false));=0A@@=20-15364,7=20=
+15385,14=20@@=20RememberAllDependentForRebuilding(AlteredTableInfo=20=
*tab,=20AlterTableType=20subtype,=0A=20=0A=20=09=09=09case=20=
ConstraintRelationId:=0A=20=09=09=09=09Assert(foundObject.objectSubId=20=
=3D=3D=200);=0A-=09=09=09=09=
RememberConstraintForRebuilding(foundObject.objectId,=20tab);=0A+=09=09=09=
=09if=20(attnum=20=3D=3D=200)=0A+=09=09=09=09{=0A+=09=09=09=09=09/*=20=
Whole-row=20CHECK=20constraints=20may=20need=20to=20be=20revalidated=20=
*/=0A+=09=09=09=09=09if=20(get_constraint_type(foundObject.objectId)=20=
=3D=3D=20CONSTRAINT_CHECK)=0A+=09=09=09=09=09=09=
RememberConstraintForRebuilding(foundObject.objectId,=20tab);=0A+=09=09=09=
=09}=0A+=09=09=09=09else=0A+=09=09=09=09=09=
RememberConstraintForRebuilding(foundObject.objectId,=20tab);=0A=20=09=09=
=09=09break;=0A=20=0A=20=09=09=09case=20ProcedureRelationId:=0A@@=20=
-15505,8=20+15533,14=20@@=20=
RememberAllDependentForRebuilding(AlteredTableInfo=20*tab,=20=
AlterTableType=20subtype,=0A=20=0A=20=09=09=09=09/*=0A=20=09=09=09=09=20=
*=20We=20don't=20expect=20any=20other=20sorts=20of=20objects=20to=20=
depend=20on=20a=0A-=09=09=09=09=20*=20column.=0A+=09=09=09=09=20*=20=
column.=20=20A=20whole-relation=20scan=20can=20find=20the=20relation's=20=
row=0A+=09=09=09=09=20*=20type,=20which=20doesn't=20need=20rebuilding=20=
for=20SET=20EXPRESSION.=0A=20=09=09=09=09=20*/=0A+=09=09=09=09if=20=
(attnum=20=3D=3D=200=20&&=0A+=09=09=09=09=09foundObject.classId=20=3D=3D=20=
TypeRelationId=20&&=0A+=09=09=09=09=09=
get_typ_typrelid(foundObject.objectId)=20=3D=3D=20RelationGetRelid(rel))=0A=
+=09=09=09=09=09continue;=0A+=0A=20=09=09=09=09elog(ERROR,=20"unexpected=20=
object=20depending=20on=20column:=20%s",=0A=20=09=09=09=09=09=20=
getObjectDescription(&foundObject,=20false));=0A=20=09=09=09=09break;=0A=
diff=20--git=20a/src/test/regress/expected/generated_virtual.out=20=
b/src/test/regress/expected/generated_virtual.out=0Aindex=20=
24d5dbf46ca..625b4025828=20100644=0A---=20=
a/src/test/regress/expected/generated_virtual.out=0A+++=20=
b/src/test/regress/expected/generated_virtual.out=0A@@=20-694,6=20=
+694,25=20@@=20INSERT=20INTO=20gtest20c=20VALUES=20(1);=20=20--=20ok=0A=20=
INSERT=20INTO=20gtest20c=20VALUES=20(NULL);=20=20--=20fails=0A=20ERROR:=20=
=20new=20row=20for=20relation=20"gtest20c"=20violates=20check=20=
constraint=20"whole_row_check"=0A=20DETAIL:=20=20Failing=20row=20=
contains=20(null,=20virtual).=0A+ALTER=20TABLE=20gtest20c=20ALTER=20=
COLUMN=20b=20SET=20EXPRESSION=20AS=20(nullif(a,=201));=20--=20fails=0A=
+ERROR:=20=20check=20constraint=20"whole_row_check"=20of=20relation=20=
"gtest20c"=20is=20violated=20by=20some=20row=0A+CREATE=20INDEX=20=
gtest20c_expr_idx=20ON=20gtest20c=20((gtest20c=20IS=20NOT=20NULL));=0A=
+CREATE=20INDEX=20gtest20c_a_idx=20ON=20gtest20c=20(a);=0A+SELECT=20=
pg_relation_filenode('gtest20c_expr_idx')=20AS=20=
gtest20c_expr_idx_filenode=20\gset=0A+SELECT=20=
pg_relation_filenode('gtest20c_a_idx')=20AS=20gtest20c_a_idx_filenode=20=
\gset=0A+ALTER=20TABLE=20gtest20c=20ALTER=20COLUMN=20b=20SET=20=
EXPRESSION=20AS=20(a=20*=203);=20--=20ok,=20rebuilds=20index=0A+SELECT=20=
pg_relation_filenode('gtest20c_expr_idx')=20<>=20=
:gtest20c_expr_idx_filenode=20AS=20whole_row_index_rebuilt;=0A+=20=
whole_row_index_rebuilt=20=0A+-------------------------=0A+=20t=0A+(1=20=
row)=0A+=0A+SELECT=20pg_relation_filenode('gtest20c_a_idx')=20=3D=20=
:gtest20c_a_idx_filenode=20AS=20ordinary_index_not_rebuilt;=0A+=20=
ordinary_index_not_rebuilt=20=0A+----------------------------=0A+=20t=0A=
+(1=20row)=0A+=0A=20--=20not-null=20constraints=0A=20CREATE=20TABLE=20=
gtest21a=20(a=20int=20PRIMARY=20KEY,=20b=20int=20GENERATED=20ALWAYS=20AS=20=
(nullif(a,=200))=20VIRTUAL=20NOT=20NULL);=0A=20INSERT=20INTO=20gtest21a=20=
(a)=20VALUES=20(1);=20=20--=20ok=0Adiff=20--git=20=
a/src/test/regress/sql/generated_virtual.sql=20=
b/src/test/regress/sql/generated_virtual.sql=0Aindex=20=
9c2bb6590b3..14fa47b6173=20100644=0A---=20=
a/src/test/regress/sql/generated_virtual.sql=0A+++=20=
b/src/test/regress/sql/generated_virtual.sql=0A@@=20-347,6=20+347,14=20=
@@=20CREATE=20TABLE=20gtest20c=20(a=20int,=20b=20int=20GENERATED=20=
ALWAYS=20AS=20(a=20*=202)=20VIRTUAL);=0A=20ALTER=20TABLE=20gtest20c=20=
ADD=20CONSTRAINT=20whole_row_check=20CHECK=20(gtest20c=20IS=20NOT=20=
NULL);=0A=20INSERT=20INTO=20gtest20c=20VALUES=20(1);=20=20--=20ok=0A=20=
INSERT=20INTO=20gtest20c=20VALUES=20(NULL);=20=20--=20fails=0A+ALTER=20=
TABLE=20gtest20c=20ALTER=20COLUMN=20b=20SET=20EXPRESSION=20AS=20=
(nullif(a,=201));=20--=20fails=0A+CREATE=20INDEX=20gtest20c_expr_idx=20=
ON=20gtest20c=20((gtest20c=20IS=20NOT=20NULL));=0A+CREATE=20INDEX=20=
gtest20c_a_idx=20ON=20gtest20c=20(a);=0A+SELECT=20=
pg_relation_filenode('gtest20c_expr_idx')=20AS=20=
gtest20c_expr_idx_filenode=20\gset=0A+SELECT=20=
pg_relation_filenode('gtest20c_a_idx')=20AS=20gtest20c_a_idx_filenode=20=
\gset=0A+ALTER=20TABLE=20gtest20c=20ALTER=20COLUMN=20b=20SET=20=
EXPRESSION=20AS=20(a=20*=203);=20--=20ok,=20rebuilds=20index=0A+SELECT=20=
pg_relation_filenode('gtest20c_expr_idx')=20<>=20=
:gtest20c_expr_idx_filenode=20AS=20whole_row_index_rebuilt;=0A+SELECT=20=
pg_relation_filenode('gtest20c_a_idx')=20=3D=20:gtest20c_a_idx_filenode=20=
AS=20ordinary_index_not_rebuilt;=0A=20=0A=20--=20not-null=20constraints=0A=
=20CREATE=20TABLE=20gtest21a=20(a=20int=20PRIMARY=20KEY,=20b=20int=20=
GENERATED=20ALWAYS=20AS=20(nullif(a,=200))=20VIRTUAL=20NOT=20NULL);=0A--=20=
=0A2.50.1=20(Apple=20Git-155)=0A=0A=

--Apple-Mail=_3E7818AA-ED84-4BC4-BC4D-420C6E508E3A--