Hi.
In case you are wondering, I already handled the whole-row cases for
ALTER TABLE DROP COLUMN and ALTER COLUMN SET DATA TYPE in
https://commitfest.postgresql.org/patch/5988 and
https://commitfest.postgresql.org/patch/6055
However, I missed the ALTER COLUMN SET EXPRESSION scenario.
RememberAllDependentForRebuilding with (attnum = 0) is essentially
asking any objects depend on this relation,
It will certainly catch many whole-row referenced dependent objects,
however, I wouldn’t be surprised if unintended corner cases exist.
CREATE TABLE r2 (a int, b int GENERATED ALWAYS AS (a * 10) STORED);
ALTER TABLE r2 ADD CONSTRAINT cc CHECK (a IS NOT NULL);
CREATE INDEX r2_idx ON r2 (a);
CREATE POLICY p3 ON r2 AS PERMISSIVE USING (a IS NOT NULL);
select classid::regclass, * from pg_depend where refobjid =
'r2'::regclass::oid and classid in ('pg_policy'::regclass);
The examples above show that RLS policies can have two dependencies on the
relation: one on the specific column, and another on the relation itself.
``RememberAllDependentForRebuilding with (attnum = 0)`` cannot cope with this.
ATRewriteTables->finish_heap_swap->reindex_relation may reindex the
relation, but
AlteredTableInfo->changedIndexOids should still remember the whole-row
Var references index objects.
For ALTER COLUMN SET EXPRESSION, no need to worry about whole-row
referenced triggers.BEFORE trigger referencing the whole-row
(including generated column) is not allowed (see
CreateTriggerFiringOn: ```if (!whenClause &&stmt->whenClause)```), and
ALTER COLUMN SET EXPRESSION will work fine with AFTER
triggersthat have whole-row reference.
The attached v2 includes support for ALTER COLUMN SET EXPRESSION on columns
referenced by whole-row indexes, check constraints, and RLS policies.
The code pattern is more or less simialr to
https://commitfest.postgresql.org/patch/6055.
I should rebase https://commitfest.postgresql.org/patch/6055 BTW.
--
jian
https://www.enterprisedb.com/
From d46834ac954b57bdcda39b95fc0f911ea61475f6 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Wed, 13 May 2026 13:08:39 +0800
Subject: [PATCH v2 1/1] Disallow or rebuild dependent while ALTER COLUMN SET
EXPRESSION
Demo:
drop table if exists r3 cascade;
CREATE TABLE r3 (a int, b int GENERATED ALWAYS AS (a * 10) STORED);
ALTER TABLE r3 ADD CONSTRAINT whole_row_check CHECK (r3 IS NOT NULL);
CREATE INDEX r3_idx ON r3 ((r3 = ROW (1, 2)));
CREATE POLICY p3 ON r3 AS PERMISSIVE USING (r3 IS NOT NULL);
-- error, cannot recreate policy currently
ALTER TABLE r3 ALTER COLUMN b SET EXPRESSION AS ( a * 10::bigint);
drop policy p3 on r3;
-- constraint will be revalidated, index will be rebuild
ALTER TABLE r3 ALTER COLUMN b SET EXPRESSION AS ( a * 10::bigint);
related: https://commitfest.postgresql.org/patch/6055
discussion: https://postgr.es/m/cajtyswxokyedvbzymwc9skrq7y_muv6xjxn4h9gfsbopd3n...@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/
---
src/backend/commands/tablecmds.c | 298 ++++++++++++++++++
src/backend/optimizer/util/var.c | 54 ++++
src/include/optimizer/optimizer.h | 1 +
.../regress/expected/generated_stored.out | 13 +
.../regress/expected/generated_virtual.out | 13 +
src/test/regress/expected/rowsecurity.out | 30 ++
src/test/regress/sql/generated_stored.sql | 16 +
src/test/regress/sql/generated_virtual.sql | 16 +
src/test/regress/sql/rowsecurity.sql | 27 ++
src/tools/pgindent/typedefs.list | 1 +
10 files changed, 469 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 88451c91448..a645ac25f0c 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -786,6 +786,11 @@ static void ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab,
static List *collectPartitionIndexExtDeps(List *partitionOids);
static void applyPartitionIndexExtDeps(Oid newPartOid, List *extDepState);
static void freePartitionIndexExtDeps(List *extDepState);
+static void RememberWholeRowDependentForRebuilding(AlteredTableInfo *tab, AlterTableType subtype,
+ Relation rel, AttrNumber attnum,
+ const char *colName);
+
+static List *GeRelAssociatedPolicies(Relation rel);
/* ----------------------------------------------------------------
* DefineRelation
@@ -8791,6 +8796,13 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
*/
RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName);
+ /*
+ * Find whole-row referenced objects that depend on the column
+ * (constraints, indexes, etc.), and record enough information to either
+ * recreate the objects or report an error.
+ */
+ RememberWholeRowDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName);
+
/*
* Drop the dependency records of the GENERATED expression, in particular
* its INTERNAL dependency on the column, which would otherwise cause
@@ -23896,3 +23908,289 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* Restore the userid and security context. */
SetUserIdAndSecContext(save_userid, save_sec_context);
}
+
+/*
+ * Record dependencies between objects with whole-row Var references
+ * (indexes, CHECK constraints, etc.) and the relation, or report an
+ * error.
+ *
+ * This function currently applies only to ALTER COLUMN SET EXPRESSION.
+ */
+static void
+RememberWholeRowDependentForRebuilding(AlteredTableInfo *tab, AlterTableType subtype,
+ Relation rel, AttrNumber attnum, const char *colName)
+{
+ Node *expr = NULL;
+ ScanKeyData skey;
+ Relation pg_index;
+ SysScanDesc indscan;
+ HeapTuple htup;
+ HeapTuple indexTuple;
+ Bitmapset *expr_attrs = NULL;
+ Datum exprDatum;
+ char *exprString;
+ bool isnull;
+ bool have_wholerow = false;
+ List *wholerow_idxoids = NIL;
+ List *pols = NIL;
+ Relation pg_policy;
+ Oid reltypid;
+ TupleConstr *constr = RelationGetDescr(rel)->constr;
+
+ Assert(subtype == AT_SetExpression);
+
+ /*
+ * Loop through each CHECK constraint, see if it contain whole-row
+ * references or not
+ */
+ if (constr && constr->num_check > 0)
+ {
+ Relation pg_constraint;
+ SysScanDesc conscan;
+
+ pg_constraint = table_open(ConstraintRelationId, AccessShareLock);
+
+ ScanKeyInit(&skey,
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+
+ conscan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, true,
+ NULL, 1, &skey);
+ while (HeapTupleIsValid(htup = systable_getnext(conscan)))
+ {
+ Form_pg_constraint conform = (Form_pg_constraint) GETSTRUCT(htup);
+
+ if (conform->contype != CONSTRAINT_CHECK)
+ continue;
+
+ exprDatum = fastgetattr(htup,
+ Anum_pg_constraint_conbin,
+ RelationGetDescr(pg_constraint), &isnull);
+ if (isnull)
+ elog(WARNING, "null conbin for relation \"%s\"",
+ RelationGetRelationName(rel));
+ else
+ {
+ char *s = TextDatumGetCString(exprDatum);
+
+ expr = stringToNode(s);
+ pfree(s);
+
+ /* Find all attributes referenced */
+ pull_varattnos(expr, 1, &expr_attrs);
+
+ have_wholerow = bms_is_member(0 - FirstLowInvalidHeapAttributeNumber,
+ expr_attrs);
+ bms_free(expr_attrs);
+ expr_attrs = NULL;
+
+ if (have_wholerow)
+ RememberConstraintForRebuilding(conform->oid, tab);
+ }
+ }
+ systable_endscan(conscan);
+ table_close(pg_constraint, AccessShareLock);
+ }
+
+ /*
+ * Now checking index whole-row references. Prepare to scan pg_index for
+ * entries having indrelid = this rel
+ */
+ have_wholerow = false;
+ ScanKeyInit(&skey,
+ Anum_pg_index_indrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+
+ pg_index = table_open(IndexRelationId, AccessShareLock);
+
+ indscan = systable_beginscan(pg_index, IndexIndrelidIndexId, true,
+ NULL, 1, &skey);
+ while (HeapTupleIsValid(indexTuple = systable_getnext(indscan)))
+ {
+ Form_pg_index index = (Form_pg_index) GETSTRUCT(indexTuple);
+
+ if (list_member_oid(wholerow_idxoids, index->indexrelid))
+ continue;
+
+ if (!heap_attisnull(indexTuple, Anum_pg_index_indexprs, NULL))
+ {
+ exprDatum = SysCacheGetAttrNotNull(INDEXRELID, indexTuple,
+ Anum_pg_index_indexprs);
+ exprString = TextDatumGetCString(exprDatum);
+ expr = (Node *) stringToNode(exprString);
+ pfree(exprString);
+
+ /* Find all attributes referenced */
+ pull_varattnos(expr, 1, &expr_attrs);
+
+ have_wholerow = bms_is_member(0 - FirstLowInvalidHeapAttributeNumber,
+ expr_attrs);
+ bms_free(expr_attrs);
+ expr_attrs = NULL;
+
+ if (have_wholerow)
+ {
+ RememberIndexForRebuilding(index->indexrelid, tab);
+ continue;
+ }
+ }
+
+ if (!heap_attisnull(indexTuple, Anum_pg_index_indpred, NULL))
+ {
+ exprDatum = SysCacheGetAttrNotNull(INDEXRELID, indexTuple,
+ Anum_pg_index_indpred);
+ exprString = TextDatumGetCString(exprDatum);
+ expr = (Node *) stringToNode(exprString);
+ pfree(exprString);
+
+ /* Find all attributes referenced */
+ pull_varattnos(expr, 1, &expr_attrs);
+
+ have_wholerow = bms_is_member(0 - FirstLowInvalidHeapAttributeNumber,
+ expr_attrs);
+ bms_free(expr_attrs);
+ expr_attrs = NULL;
+
+ if (have_wholerow)
+ RememberIndexForRebuilding(index->indexrelid, tab);
+ }
+ }
+ systable_endscan(indscan);
+
+ table_close(pg_index, AccessShareLock);
+
+ /*
+ * No need to check trigger whole-row references. Creation of triggers
+ * with whole-row Vars referencing generated columns is disallowed; see
+ * CreateTriggerFiringOn().
+ */
+
+ /* Now checking policy whole-row references */
+ have_wholerow = false;
+ reltypid = get_rel_type_id(RelationGetRelid(rel));
+
+ pg_policy = table_open(PolicyRelationId, AccessShareLock);
+
+ pols = GeRelAssociatedPolicies(rel);
+
+ foreach_oid(policyoid, pols)
+ {
+ SysScanDesc sscan;
+ HeapTuple policy_tuple;
+ ScanKeyData polskey[1];
+ ObjectAddress pol_obj;
+
+ ScanKeyInit(&polskey[0],
+ Anum_pg_policy_oid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(policyoid));
+ sscan = systable_beginscan(pg_policy,
+ PolicyOidIndexId, true, NULL, 1,
+ polskey);
+ while (HeapTupleIsValid(policy_tuple = systable_getnext(sscan)))
+ {
+ Form_pg_policy policy = (Form_pg_policy) GETSTRUCT(policy_tuple);
+
+ exprDatum = heap_getattr(policy_tuple, Anum_pg_policy_polqual,
+ RelationGetDescr(pg_policy),
+ &isnull);
+ if (!isnull)
+ {
+ exprString = TextDatumGetCString(exprDatum);
+ expr = (Node *) stringToNode(exprString);
+ pfree(exprString);
+
+ have_wholerow = exprContainWholeRow(expr, reltypid);
+
+ if (have_wholerow)
+ {
+ pol_obj.classId = PolicyRelationId;
+ pol_obj.objectId = policy->oid;
+ pol_obj.objectSubId = 0;
+
+ if (subtype == AT_SetExpression)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter generation expression of a column used in a policy definition"),
+ errdetail("%s depends on column \"%s\"",
+ getObjectDescription(&pol_obj, false),
+ colName));
+ }
+ }
+
+ exprDatum = heap_getattr(policy_tuple, Anum_pg_policy_polwithcheck,
+ RelationGetDescr(pg_policy),
+ &isnull);
+ if (!isnull)
+ {
+ exprString = TextDatumGetCString(exprDatum);
+ expr = (Node *) stringToNode(exprString);
+ pfree(exprString);
+
+ have_wholerow = exprContainWholeRow(expr, reltypid);
+
+ if (have_wholerow)
+ {
+ pol_obj.classId = PolicyRelationId;
+ pol_obj.objectId = policy->oid;
+ pol_obj.objectSubId = 0;
+
+ if (subtype == AT_SetExpression)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter generation expression of a column used in a policy definition"),
+ errdetail("%s depends on column \"%s\"",
+ getObjectDescription(&pol_obj, false),
+ colName));
+ }
+ }
+ }
+ systable_endscan(sscan);
+ }
+ table_close(pg_policy, AccessShareLock);
+}
+
+static List *
+GeRelAssociatedPolicies(Relation rel)
+{
+ Relation depRel;
+ ScanKeyData key[3];
+ SysScanDesc scan;
+ HeapTuple depTup;
+ List *result = NIL;
+
+ depRel = table_open(DependRelationId, RowExclusiveLock);
+ ScanKeyInit(&key[0],
+ Anum_pg_depend_refclassid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationRelationId));
+ ScanKeyInit(&key[1],
+ Anum_pg_depend_refobjid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ ScanKeyInit(&key[2],
+ Anum_pg_depend_refobjsubid,
+ BTEqualStrategyNumber, F_INT4EQ,
+ Int32GetDatum((int32) 0));
+
+ scan = systable_beginscan(depRel, DependReferenceIndexId, true,
+ NULL, 3, key);
+ while (HeapTupleIsValid(depTup = systable_getnext(scan)))
+ {
+ Form_pg_depend foundDep = (Form_pg_depend) GETSTRUCT(depTup);
+ ObjectAddress foundObject;
+
+ foundObject.classId = foundDep->classid;
+ foundObject.objectId = foundDep->objid;
+ foundObject.objectSubId = foundDep->objsubid;
+
+ if (foundObject.classId == PolicyRelationId)
+ result = list_append_unique_oid(result, foundObject.objectId);
+ }
+ systable_endscan(scan);
+ table_close(depRel, NoLock);
+
+ return result;
+}
diff --git a/src/backend/optimizer/util/var.c b/src/backend/optimizer/util/var.c
index 907a255c36f..d9b421517cb 100644
--- a/src/backend/optimizer/util/var.c
+++ b/src/backend/optimizer/util/var.c
@@ -49,6 +49,11 @@ typedef struct
int sublevels_up;
} pull_vars_context;
+typedef struct
+{
+ Oid reltypid; /* the whole-row typeid */
+} contain_wholerow_context;
+
typedef struct
{
int var_location;
@@ -73,6 +78,7 @@ typedef struct
static bool pull_varnos_walker(Node *node,
pull_varnos_context *context);
static bool pull_varattnos_walker(Node *node, pull_varattnos_context *context);
+static bool exprContainWholeRow_walker(Node *node, contain_wholerow_context *context);
static bool pull_vars_walker(Node *node, pull_vars_context *context);
static bool contain_var_clause_walker(Node *node, void *context);
static bool contain_vars_of_level_walker(Node *node, int *sublevels_up);
@@ -327,6 +333,54 @@ pull_varattnos_walker(Node *node, pull_varattnos_context *context)
return expression_tree_walker(node, pull_varattnos_walker, context);
}
+static bool
+exprContainWholeRow_walker(Node *node, contain_wholerow_context *context)
+{
+ if (node == NULL)
+ return false;
+
+ if (IsA(node, Var))
+ {
+ Var *var = (Var *) node;
+
+ if (var->varattno == InvalidAttrNumber &&
+ var->vartype == context->reltypid)
+ return true;
+
+ return false;
+ }
+
+ if (IsA(node, Query))
+ return query_tree_walker((Query *) node, exprContainWholeRow_walker,
+ context, 0);
+
+ return expression_tree_walker(node, exprContainWholeRow_walker, context);
+}
+
+/*
+ * exprContainWholeRow -
+ *
+ * Determine whether an expression contains whole-row Var reference, recursing as needed.
+ * For simple expressions without sublinks, pull_varattnos is usually sufficient
+ * to detect a whole-row Var. But if the node contains sublinks (unplanned
+ * subqueries), the check must instead rely on the whole-row type OID.
+ * Use exprContainWholeRow to check whole-row var existsence when in doubt.
+ */
+bool
+exprContainWholeRow(Node *node, Oid reltypid)
+{
+ contain_wholerow_context context;
+
+ context.reltypid = reltypid;
+
+ Assert(OidIsValid(reltypid));
+
+ return query_or_expression_tree_walker(node,
+ exprContainWholeRow_walker,
+ &context,
+ 0);
+}
+
/*
* pull_vars_of_level
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index cb6241e2bdd..c9935064c4b 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -206,6 +206,7 @@ extern SortGroupClause *get_sortgroupref_clause_noerr(Index sortref,
extern Bitmapset *pull_varnos(PlannerInfo *root, Node *node);
extern Bitmapset *pull_varnos_of_level(PlannerInfo *root, Node *node, int levelsup);
extern void pull_varattnos(Node *node, Index varno, Bitmapset **varattnos);
+extern bool exprContainWholeRow(Node *node, Oid reltypid);
extern List *pull_vars_of_level(Node *node, int levelsup);
extern bool contain_var_clause(Node *node);
extern bool contain_vars_of_level(Node *node, int levelsup);
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index 7866ae0ebbe..313353ab237 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -688,6 +688,19 @@ 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
+-- indedx with whole-row reference need rebuild
+CREATE TABLE gtest20d (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
+INSERT INTO gtest20d VALUES (1), (1);
+CREATE INDEX gtest20d_idx1 ON gtest20d (a) WHERE gtest20d = ROW (1, 2);
+ALTER TABLE gtest20d ALTER COLUMN b SET EXPRESSION AS ( a * 2::bigint); -- index rebuild
+CREATE INDEX gtest20d_idx2 ON gtest20d ((gtest20d = ROW (1, 2)));
+ALTER TABLE gtest20d ALTER COLUMN b SET EXPRESSION AS ( a * 3); -- index rebuild
+-- BEFORE triggers with whole-row references are not allowed when any column is a generated column.
+-- ALTER COLUMN SET EXPRESSION works fine with AFFTER TRIGGER
+-- RLS Policies with whole-row references (some columns are stored generated
+-- columns) are tested in rowsecurity.sql
-- 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..129cf14f675 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -694,6 +694,19 @@ 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
+-- indedx with whole-row reference need rebuild
+CREATE TABLE gtest20d (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
+INSERT INTO gtest20d VALUES (1), (1);
+CREATE INDEX gtest20d_idx1 ON gtest20d (a) WHERE gtest20d = ROW (1, 2);
+ALTER TABLE gtest20d ALTER COLUMN b SET EXPRESSION AS ( a * 2::bigint); -- index rebuild
+CREATE INDEX gtest20d_idx2 ON gtest20d ((gtest20d = ROW (1, 2)));
+ALTER TABLE gtest20d ALTER COLUMN b SET EXPRESSION AS ( a * 3); -- index rebuild
+-- BEFORE triggers with whole-row references are not allowed when any column is a generated column.
+-- ALTER COLUMN SET EXPRESSION works fine with AFFTER TRIGGER
+-- RLS Policies with whole-row references (some columns are virtual generated
+-- columns) are tested in rowsecurity.sql
-- 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/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 3a5e82c35bd..597bded3b04 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -4784,7 +4784,37 @@ SELECT * FROM r1;
3 | 30 | 300
(2 rows)
+CREATE TABLE r2 (LIKE r1 INCLUDING ALL);
+CREATE POLICY p3 ON r1 AS PERMISSIVE
+ USING (a IS NOT NULL AND
+ (WITH cte AS (SELECT TRUE FROM r2
+ WHERE EXISTS (SELECT r1 FROM r2 WHERE r2 IS NULL))
+ SELECT * FROM cte));
+ALTER TABLE r1 ALTER COLUMN b SET EXPRESSION AS (a * 10::bigint); -- error
+ERROR: cannot alter generation expression of a column used in a policy definition
+DETAIL: policy p3 on table r1 depends on column "b"
+ALTER TABLE r2 ALTER COLUMN b SET EXPRESSION AS (a * 10::bigint); -- error
+ERROR: cannot alter generation expression of a column used in a policy definition
+DETAIL: policy p3 on table r1 depends on column "b"
+CREATE TABLE r3 (a int, b int GENERATED ALWAYS AS (a * 10) STORED);
+CREATE TABLE r4 (LIKE r3 INCLUDING ALL);
+ALTER TABLE r3 ENABLE ROW LEVEL SECURITY;
+ALTER TABLE r3 FORCE ROW LEVEL SECURITY;
+CREATE POLICY p3 ON r3 AS PERMISSIVE
+ USING (a IS NOT NULL AND
+ (WITH cte AS (SELECT TRUE FROM r4
+ WHERE EXISTS (SELECT r3 FROM r4 WHERE r4 IS NULL))
+ SELECT * FROM cte));
+ALTER TABLE r3 ALTER COLUMN b SET EXPRESSION AS (a * 10::bigint); -- error
+ERROR: cannot alter generation expression of a column used in a policy definition
+DETAIL: policy p3 on table r3 depends on column "b"
+ALTER TABLE r4 ALTER COLUMN b SET EXPRESSION AS (a * 10::bigint); -- error
+ERROR: cannot alter generation expression of a column used in a policy definition
+DETAIL: policy p3 on table r3 depends on column "b"
DROP TABLE r1;
+DROP TABLE r2;
+DROP TABLE r3;
+DROP TABLE r4;
-- Check dependency handling
RESET SESSION AUTHORIZATION;
CREATE TABLE dep1 (c1 int);
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql
index 6746cd4632b..fa6600cc741 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -341,6 +341,22 @@ 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
+
+-- indedx with whole-row reference need rebuild
+CREATE TABLE gtest20d (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
+INSERT INTO gtest20d VALUES (1), (1);
+CREATE INDEX gtest20d_idx1 ON gtest20d (a) WHERE gtest20d = ROW (1, 2);
+
+ALTER TABLE gtest20d ALTER COLUMN b SET EXPRESSION AS ( a * 2::bigint); -- index rebuild
+CREATE INDEX gtest20d_idx2 ON gtest20d ((gtest20d = ROW (1, 2)));
+ALTER TABLE gtest20d ALTER COLUMN b SET EXPRESSION AS ( a * 3); -- index rebuild
+
+-- BEFORE triggers with whole-row references are not allowed when any column is a generated column.
+-- ALTER COLUMN SET EXPRESSION works fine with AFFTER TRIGGER
+
+-- RLS Policies with whole-row references (some columns are stored generated
+-- columns) are tested in rowsecurity.sql
-- 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..995f3186c40 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -347,6 +347,22 @@ 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
+
+-- indedx with whole-row reference need rebuild
+CREATE TABLE gtest20d (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
+INSERT INTO gtest20d VALUES (1), (1);
+CREATE INDEX gtest20d_idx1 ON gtest20d (a) WHERE gtest20d = ROW (1, 2);
+
+ALTER TABLE gtest20d ALTER COLUMN b SET EXPRESSION AS ( a * 2::bigint); -- index rebuild
+CREATE INDEX gtest20d_idx2 ON gtest20d ((gtest20d = ROW (1, 2)));
+ALTER TABLE gtest20d ALTER COLUMN b SET EXPRESSION AS ( a * 3); -- index rebuild
+
+-- BEFORE triggers with whole-row references are not allowed when any column is a generated column.
+-- ALTER COLUMN SET EXPRESSION works fine with AFFTER TRIGGER
+
+-- RLS Policies with whole-row references (some columns are virtual generated
+-- columns) are tested in rowsecurity.sql
-- not-null constraints
CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL NOT NULL);
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 6b3566271df..45125aac15e 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -2295,7 +2295,34 @@ INSERT INTO r1 VALUES (4);
INSERT INTO r1 VALUES (3);
SELECT * FROM r1;
+CREATE TABLE r2 (LIKE r1 INCLUDING ALL);
+CREATE POLICY p3 ON r1 AS PERMISSIVE
+ USING (a IS NOT NULL AND
+ (WITH cte AS (SELECT TRUE FROM r2
+ WHERE EXISTS (SELECT r1 FROM r2 WHERE r2 IS NULL))
+ SELECT * FROM cte));
+
+ALTER TABLE r1 ALTER COLUMN b SET EXPRESSION AS (a * 10::bigint); -- error
+ALTER TABLE r2 ALTER COLUMN b SET EXPRESSION AS (a * 10::bigint); -- error
+
+CREATE TABLE r3 (a int, b int GENERATED ALWAYS AS (a * 10) STORED);
+CREATE TABLE r4 (LIKE r3 INCLUDING ALL);
+ALTER TABLE r3 ENABLE ROW LEVEL SECURITY;
+ALTER TABLE r3 FORCE ROW LEVEL SECURITY;
+
+CREATE POLICY p3 ON r3 AS PERMISSIVE
+ USING (a IS NOT NULL AND
+ (WITH cte AS (SELECT TRUE FROM r4
+ WHERE EXISTS (SELECT r3 FROM r4 WHERE r4 IS NULL))
+ SELECT * FROM cte));
+
+ALTER TABLE r3 ALTER COLUMN b SET EXPRESSION AS (a * 10::bigint); -- error
+ALTER TABLE r4 ALTER COLUMN b SET EXPRESSION AS (a * 10::bigint); -- error
+
DROP TABLE r1;
+DROP TABLE r2;
+DROP TABLE r3;
+DROP TABLE r4;
-- Check dependency handling
RESET SESSION AUTHORIZATION;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 06532bf7152..33458e71be4 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3672,6 +3672,7 @@ config_handle
config_var_value
contain_aggs_of_level_context
contain_placeholder_references_context
+contain_wholerow_context
convert_testexpr_context
copy_data_dest_cb
copy_data_source_cb
--
2.34.1
