| 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 |
| 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 |