On Fri, May 15, 2026 at 1:06 AM Ayush Tiwari <[email protected]> wrote: > > I've two minor follow-up comments/questions: > > 1. The "-- indedx with whole-row reference need rebuild" comment is > still in the new SQL/expected blocks for both generated_stored and > generated_virtual: > "-- index with whole-row reference needs rebuild" > Thanks for finding the typo!
I did another round of code cleanup and cosmetic refactoring. Main idea still the same: loop through pg_constraint, pg_index, and pg_policy to locate all objects containing whole-row references, then error reporting or remember them for recreation. The commit message was also updated. Later, I will add this thread to https://wiki.postgresql.org/wiki/PostgreSQL_19_Open_Items > 2. The new policy error message: > > errmsg("ALTER TABLE / SET EXPRESSION is not supported for > generated columns in tables that are part of a policy > definition"), > errdetail("%s contains whole row references.", ...) > > I still find this wording a bit awkward. Quoting "ALTER TABLE / > SET EXPRESSION" as a syntactic form in the message is unusual for > tablecmds.c, and the sentence is long. Could we keep it closer to > the surrounding style. > + if (subtype == AT_SetExpression) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot alter generation expression of table %s because %s uses its row type", + RelationGetRelationName(rel), + getObjectDescription(&pol_obj, false)), + errdetail("You might need to drop %s first.", getObjectDescription(&pol_obj, false))); What do you think? -- jian https://www.enterprisedb.com/
From 96980d194ad8601531541729030e80ddb5576dd1 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Fri, 15 May 2026 10:37:15 +0800 Subject: [PATCH v4 1/1] Disallow or rebuild dependent while ALTER COLUMN SET EXPRESSION MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit When changing the expression of a generated column via ALTER TABLE ALTER COLUMN SET EXPRESSION, objects that depend on the column via indirect whole-row references (such as CHECK constraints, indexes, and policies) must be handled specially, because technically pg_depend does not contain such dependencies, see recordDependencyOnSingleRelExpr, find_expr_references_walker. - If a table has policies that use whole-row references, ALTER TABLE ... ALTER COLUMN SET EXPRESSION is disallowed (such policies cannot be automatically recreated yet). - CHECK constraints are re-verified and indexes are rebuilt. For example: CREATE INDEX r3_idx ON r3 ((r3 = ROW (1, 2))); ALTER COLUMN SET EXPRESSION does not fundamentally alter the row type — it only changes the column’s generation expression. Rebuilding the index should be fine, unlike the case with ALTER COLUMN SET DATA TYPE. 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); -- Currently errors (policies cannot yet be recreated) ALTER TABLE r3 ALTER COLUMN b SET EXPRESSION AS (a * 10::bigint); -- Constraint is rechecked and index is rebuilt ALTER TABLE r3 ALTER COLUMN b SET EXPRESSION AS (a * 10::bigint); related: https://commitfest.postgresql.org/patch/6055 discussion: CAJTYsWXOkyeDVbzymWc9sKrq7Y_MUv6XJXN4H9GfsBOPd3NJ+w@mail.gmail.com">https://postgr.es/m/CAJTYsWXOkyeDVbzymWc9sKrq7Y_MUv6XJXN4H9GfsBOPd3NJ+w@mail.gmail.com commitfest entry: https://commitfest.postgresql.org/patch/ --- src/backend/commands/tablecmds.c | 307 ++++++++++++++++++ src/backend/optimizer/util/var.c | 55 ++++ 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, 479 insertions(+) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 92b0f38c353..d5cbacf3648 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 *GetRelAssociatedPolicies(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,298 @@ 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. + * + * Also see RememberAllDependentForRebuilding, which handles non-whole-row Var + * references. + * + * 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_constraint; + Relation pg_index; + Relation pg_policy; + SysScanDesc conscan; + SysScanDesc indscan; + HeapTuple constrTuple; + HeapTuple indexTuple; + Datum exprDatum; + char *exprString; + bool isnull; + List *pols = NIL; + Oid reltypid; + + Assert(subtype == AT_SetExpression); + + /* + * Now checking CHECK constraint with whole-row references + */ + if (RelationGetDescr(rel)->constr && + RelationGetDescr(rel)->constr->num_check > 0) + { + 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(constrTuple = systable_getnext(conscan))) + { + Form_pg_constraint conform = (Form_pg_constraint) GETSTRUCT(constrTuple); + + if (conform->contype != CONSTRAINT_CHECK) + continue; + + exprDatum = fastgetattr(constrTuple, + Anum_pg_constraint_conbin, + RelationGetDescr(pg_constraint), + &isnull); + if (isnull) + elog(WARNING, "null conbin for relation \"%s\"", + RelationGetRelationName(rel)); + else + { + Bitmapset *expr_attrs = NULL; + + exprString = TextDatumGetCString(exprDatum); + expr = (Node *) stringToNode(exprString); + pfree(exprString); + + /* Find all attributes referenced */ + pull_varattnos(expr, 1, &expr_attrs); + + /* + * If the CHECK constraint contains whole-row reference then + * remember it + */ + if (bms_is_member(InvalidAttrNumber - FirstLowInvalidHeapAttributeNumber, + expr_attrs)) + { + RememberConstraintForRebuilding(conform->oid, tab); + } + } + } + systable_endscan(conscan); + table_close(pg_constraint, AccessShareLock); + } + + /* + * Now checking index with whole-row references. Prepare to scan pg_index + * for entries having indrelid = this rel + */ + 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 (!heap_attisnull(indexTuple, Anum_pg_index_indexprs, NULL)) + { + Bitmapset *expr_attrs = 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); + + /* + * If the index expression contains whole-row reference then + * remember it + */ + if (bms_is_member(InvalidAttrNumber - FirstLowInvalidHeapAttributeNumber, + expr_attrs)) + { + RememberIndexForRebuilding(index->indexrelid, tab); + continue; + } + } + + if (!heap_attisnull(indexTuple, Anum_pg_index_indpred, NULL)) + { + Bitmapset *expr_attrs = 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); + + /* + * If the index predicate expression contains whole-row reference + * then remember it + */ + if (bms_is_member(InvalidAttrNumber - FirstLowInvalidHeapAttributeNumber, + expr_attrs)) + { + RememberIndexForRebuilding(index->indexrelid, tab); + } + } + } + systable_endscan(indscan); + table_close(pg_index, AccessShareLock); + + /* + * No need to check trigger with whole-row references. Creation of BEFORE + * triggers with whole-row Vars referencing (some column is generated + * column) is disallowed; see CreateTriggerFiringOn(). + */ + + /* Now checking policy whole-row references */ + reltypid = get_rel_type_id(RelationGetRelid(rel)); + + pg_policy = table_open(PolicyRelationId, AccessShareLock); + + pols = GetRelAssociatedPolicies(rel); + + foreach_oid(policyoid, pols) + { + SysScanDesc sscan; + HeapTuple policy_tuple; + ScanKeyData polskey[1]; + + 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); + + if (expr_contain_wholerow(expr, reltypid)) + { + ObjectAddress pol_obj; + + ObjectAddressSet(pol_obj, PolicyRelationId, policy->oid); + + if (subtype == AT_SetExpression) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot alter generation expression of table %s because %s uses its row type", + RelationGetRelationName(rel), + getObjectDescription(&pol_obj, false)), + errdetail("You might need to drop %s first.", getObjectDescription(&pol_obj, false))); + } + } + + exprDatum = heap_getattr(policy_tuple, Anum_pg_policy_polwithcheck, + RelationGetDescr(pg_policy), + &isnull); + if (!isnull) + { + exprString = TextDatumGetCString(exprDatum); + expr = (Node *) stringToNode(exprString); + pfree(exprString); + + if (expr_contain_wholerow(expr, reltypid)) + { + ObjectAddress pol_obj; + + ObjectAddressSet(pol_obj, PolicyRelationId, policy->oid); + + if (subtype == AT_SetExpression) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot alter generation expression of table %s because %s uses its row type", + RelationGetRelationName(rel), + getObjectDescription(&pol_obj, false)), + errdetail("You might need to drop %s first.", getObjectDescription(&pol_obj, false))); + } + } + } + systable_endscan(sscan); + } + table_close(pg_policy, AccessShareLock); +} + +/* + * GetRelAssociatedPolicies + * + * Returns a list of OIDs of all row-level security policies associated with the + * given relation. + */ +static List * +GetRelAssociatedPolicies(Relation rel) +{ + Relation depRel; + ScanKeyData key[3]; + SysScanDesc scan; + HeapTuple depTup; + List *result = NIL; + + depRel = table_open(DependRelationId, AccessShareLock); + 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); + + if (foundDep->classid == PolicyRelationId) + result = list_append_unique_oid(result, foundDep->objid); + } + systable_endscan(scan); + table_close(depRel, AccessShareLock); + + return result; +} diff --git a/src/backend/optimizer/util/var.c b/src/backend/optimizer/util/var.c index 907a255c36f..7ebc5b48a68 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 expr_contain_wholerow_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,55 @@ pull_varattnos_walker(Node *node, pull_varattnos_context *context) return expression_tree_walker(node, pull_varattnos_walker, context); } +static bool +expr_contain_wholerow_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, expr_contain_wholerow_walker, + context, 0); + + return expression_tree_walker(node, expr_contain_wholerow_walker, context); +} + +/* + * expr_contain_wholerow - + * + * 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 expr_contain_wholerow to check whole-row var existence when in doubt. + */ +bool +expr_contain_wholerow(Node *node, Oid reltypid) +{ + contain_wholerow_context context; + + context.reltypid = reltypid; + + Assert(OidIsValid(reltypid)); + + return query_or_expression_tree_walker(node, + expr_contain_wholerow_walker, + &context, + 0); +} + /* * pull_vars_of_level diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h index cb6241e2bdd..0491059c7cc 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 expr_contain_wholerow(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..13423f05eb8 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 +-- index with whole-row reference needs 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 AFTER 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..7c42220876c 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 +-- index with whole-row reference needs rebuild +CREATE TABLE gtest20d (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL); +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 AFTER 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..8a7bb1798d1 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 table r1 because policy p3 on table r1 uses its row type +DETAIL: You might need to drop policy p3 on table r1 first. +ALTER TABLE r2 ALTER COLUMN b SET EXPRESSION AS (a * 10::bigint); -- error +ERROR: cannot alter generation expression of table r2 because policy p3 on table r1 uses its row type +DETAIL: You might need to drop policy p3 on table r1 first. +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 table r3 because policy p3 on table r3 uses its row type +DETAIL: You might need to drop policy p3 on table r3 first. +ALTER TABLE r4 ALTER COLUMN b SET EXPRESSION AS (a * 10::bigint); -- error +ERROR: cannot alter generation expression of table r4 because policy p3 on table r3 uses its row type +DETAIL: You might need to drop policy p3 on table r3 first. 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..c156275ed47 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 + +-- index with whole-row reference needs 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 AFTER 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..2724db3c615 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 + +-- index with whole-row reference needs rebuild +CREATE TABLE gtest20d (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL); +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 AFTER 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 cbd9e10fc1d..fd1f89ab9ed 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -3691,6 +3691,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
