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/
v1-0001-Fix-SET-EXPRESSION-with-whole-row-virtual-column-.patch
Description: Binary data
