Fix SET EXPRESSION for virtual columns with whole-row dependencies

From: Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com>
To: Postgres hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: Peter Eisentraut <peter(at)eisentraut(dot)org>, jian he <jian(dot)universality(at)gmail(dot)com>
Subject: Fix SET EXPRESSION for virtual columns with whole-row dependencies
Date: 2026-06-09 06:22:32
Message-ID: 71242FEA-15A3-4073-9FA1-561CA744ACBD@gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

While testing “[f80bedd52] Allow ALTER COLUMN SET EXPRESSION on virtual columns”, I found that the feature missed handling whole-row check constraints.

Here is a repro:
```
evantest=# 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=# insert into t(a) values(1);
INSERT 0 1
evantest=# alter table t alter b set expression as (nullif(a, 1));
ALTER TABLE
evantest=# 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 = 1:
```
evantest=# 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=# 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’s attnum, and colName is b, so it doesn’t 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 == 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=# 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/

Attachment Content-Type Size
v1-0001-Fix-SET-EXPRESSION-with-whole-row-virtual-column-.patch application/octet-stream 7.5 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Nitin Motiani 2026-06-09 06:34:01 Re: Adding pg_dump flag for parallel export to pipes
Previous Message Xuneng Zhou 2026-06-09 06:13:54 Re: t/035_standby_logical_decoding.pl might fail on attempt to read wrong timeline