v1-0001-Rebuild-CHECK-constraints-for-SET-EXPRESSION.patch
application/octet-stream
Filename: v1-0001-Rebuild-CHECK-constraints-for-SET-EXPRESSION.patch
Type: application/octet-stream
Part: 0
From 0000000000000000000000000000000000000000 Mon Sep 17 00:00:00 2001
From: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Date: Mon, 11 May 2026 10:50:38 +0530
Subject: [PATCH v1] Rebuild CHECK constraints after generated column SET
EXPRESSION
ALTER TABLE ... ALTER COLUMN ... SET EXPRESSION rebuilds objects found by
scanning dependencies on the generated column's attnum. That misses CHECK
constraints that contain a whole-row reference, because whole-row Vars do not
record per-column dependencies, even though generated columns are part of the
row value seen by such constraints.
Remember all CHECK constraints on the relation during SET EXPRESSION, in
addition to objects found by the per-column dependency scan. This keeps the
logic simple; RememberConstraintForRebuilding() already de-duplicates
constraints found through both paths.
Add tests for both stored and virtual generated columns.
---
src/backend/commands/tablecmds.c | 53 +++++++++++++++++++++++++
src/test/regress/expected/generated_stored.out | 2 +
src/test/regress/expected/generated_virtual.out | 2 +
src/test/regress/sql/generated_stored.sql | 1 +
src/test/regress/sql/generated_virtual.sql | 1 +
5 files changed, 59 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 88451c91448..3770933e4ae 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -686,6 +686,8 @@ static ObjectAddress ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
AlterTableCmd *cmd, LOCKMODE lockmode);
static void RememberAllDependentForRebuilding(AlteredTableInfo *tab, AlterTableType subtype,
Relation rel, AttrNumber attnum, const char *colName);
+static void RememberAllCheckConstraintsForRebuilding(AlteredTableInfo *tab,
+ Relation rel);
static void RememberConstraintForRebuilding(Oid conoid, AlteredTableInfo *tab);
static void RememberIndexForRebuilding(Oid indoid, AlteredTableInfo *tab);
static void RememberStatisticsForRebuilding(Oid stxoid, AlteredTableInfo *tab);
@@ -8788,8 +8790,15 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
/*
* Find everything that depends on the column (constraints, indexes, etc),
* and record enough information to let us recreate the objects.
+ *
+ * In addition to objects depending on this column, also rebuild every
+ * CHECK constraint on the table. Some CHECK constraints (notably those
+ * containing whole-row references) have no per-column dependency on the
+ * generated column but can still be affected by changing its expression,
+ * so we cannot rely on the dependency scan alone to find them.
*/
RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName);
+ RememberAllCheckConstraintsForRebuilding(tab, rel);
/*
* Drop the dependency records of the GENERATED expression, in particular
@@ -15533,6 +15542,50 @@ RememberAllDependentForRebuilding(AlteredTableInfo *tab, AlterTableType subtype,
table_close(depRel, NoLock);
}
+/*
+ * Subroutine for ATExecSetExpression: remember every CHECK constraint on the
+ * relation so that it gets rebuilt as needed.
+ *
+ * The per-column dependency scan done by RememberAllDependentForRebuilding()
+ * misses CHECK constraints that reference the generated column only through
+ * a whole-row Var, because whole-row Vars do not record per-column
+ * dependencies. Rather than re-examining each constraint's expression to
+ * decide whether it could be affected, we simply queue all CHECK constraints
+ * for rebuild; RememberConstraintForRebuilding() de-duplicates against those
+ * already remembered via the dependency scan.
+ */
+static void
+RememberAllCheckConstraintsForRebuilding(AlteredTableInfo *tab, Relation rel)
+{
+ Relation conRel;
+ ScanKeyData key[1];
+ SysScanDesc scan;
+ HeapTuple conTup;
+
+ conRel = table_open(ConstraintRelationId, AccessShareLock);
+
+ ScanKeyInit(&key[0],
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+
+ scan = systable_beginscan(conRel, ConstraintRelidTypidNameIndexId,
+ true, NULL, 1, key);
+
+ while (HeapTupleIsValid(conTup = systable_getnext(scan)))
+ {
+ Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(conTup);
+
+ if (con->contype != CONSTRAINT_CHECK)
+ continue;
+
+ RememberConstraintForRebuilding(con->oid, tab);
+ }
+
+ systable_endscan(scan);
+ table_close(conRel, AccessShareLock);
+}
+
/*
* Subroutine for ATExecAlterColumnType: remember that a replica identity
* needs to be reset.
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index 7866ae0ebbe..93e5aca29b6 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -688,6 +688,8 @@ INSERT INTO gtest20c VALUES (1); -- ok
INSERT INTO gtest20c VALUES (NULL); -- fails
ERROR: new row for relation "gtest20c" violates check constraint "whole_row_check"
DETAIL: Failing row contains (null, null).
+ALTER TABLE gtest20c ALTER COLUMN b SET EXPRESSION AS (NULL::int); -- violates constraint
+ERROR: check constraint "whole_row_check" of relation "gtest20c" is violated by some row
-- not-null constraints
CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED NOT NULL);
INSERT INTO gtest21a (a) VALUES (1); -- ok
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index 24d5dbf46ca..fab119a2ca0 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -694,6 +694,8 @@ INSERT INTO gtest20c VALUES (1); -- ok
INSERT INTO gtest20c VALUES (NULL); -- fails
ERROR: new row for relation "gtest20c" violates check constraint "whole_row_check"
DETAIL: Failing row contains (null, virtual).
+ALTER TABLE gtest20c ALTER COLUMN b SET EXPRESSION AS (NULL::int); -- violates constraint
+ERROR: check constraint "whole_row_check" of relation "gtest20c" is violated by some row
-- not-null constraints
CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL NOT NULL);
INSERT INTO gtest21a (a) VALUES (1); -- ok
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql
index 6746cd4632b..eb8f4a5a90e 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -341,6 +341,7 @@ CREATE TABLE gtest20c (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
ALTER TABLE gtest20c ADD CONSTRAINT whole_row_check CHECK (gtest20c IS NOT NULL);
INSERT INTO gtest20c VALUES (1); -- ok
INSERT INTO gtest20c VALUES (NULL); -- fails
+ALTER TABLE gtest20c ALTER COLUMN b SET EXPRESSION AS (NULL::int); -- violates constraint
-- not-null constraints
CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED NOT NULL);
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index 9c2bb6590b3..da422a83e42 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -347,6 +347,7 @@ CREATE TABLE gtest20c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
ALTER TABLE gtest20c ADD CONSTRAINT whole_row_check CHECK (gtest20c IS NOT NULL);
INSERT INTO gtest20c VALUES (1); -- ok
INSERT INTO gtest20c VALUES (NULL); -- fails
+ALTER TABLE gtest20c ALTER COLUMN b SET EXPRESSION AS (NULL::int); -- violates constraint
-- not-null constraints
CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL NOT NULL);
--
2.34.1