On Mon, Sep 15, 2025 at 8:40 PM jian he <[email protected]> wrote:
>
> Summary of attached v4:
> v4-0001: Handles ALTER TABLE DROP COLUMN when whole-row Vars are
> referenced in check constraints and indexes.
>
> v4-0002: Handles ALTER TABLE ALTER COLUMN SET DATA TYPE when whole-row
> Vars are referenced in check constraints and indexes.
>
> v4-0003: Handle ALTER TABLE ALTER COLUMN SET DATA TYPE and ALTER TABLE DROP
> COLUMN when policy objects reference whole-row Vars. Policy quals and check
> quals may contain whole-row Vars and can include sublinks (unplanned
> subqueries), pull_varattnos is not enough to locate whole-row Var. Instead,
> obtain the whole-row type OID and recursively check each Var in expression
> node
> to see if its vartype matches the whole-row type OID.
in v4, I use
+ TupleConstr *constr = RelationGetDescr(rel)->constr;
+
+ if (constr && constr->num_check > 0)
+{
+ systable_beginscan
+}
to check if a relation's check constraint expression contains a whole-row or
not. however this will have multiple systable_beginscan if multiple check
constraints contain wholerow expr.
I changed it to systable_beginscan pg_constraint once and check if the scan
returned pg_constraint tuple meets our condition or not.
and some minor adjustments to regression tests.
From ea5f731dbd1c309a9a5a5d3119bd554e59b2a2ea Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Tue, 23 Sep 2025 13:31:03 +0800
Subject: [PATCH v5 1/3] ALTER TABLE DROP COLUMN drop wholerow referenced
object
CREATE TABLE ts (a int, c int, b int constraint cc check((ts = ROW(1,1,1))));
CREATE INDEX tsi3 on ts ((ts is null));
CREATE INDEX tsi4 on ts (b) where ts is not null;
ALTER TABLE ts DROP COLUMN a CASCADE;
will drop above all indexes, constraints on the table ts.
now
\d ts
Table "public.ts"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
c | integer | | |
b | integer | | |
discussion: https://postgr.es/m/cacjufxga6kvqy7dbhglvw9s9kkmpgyzt5me6c7kefjdpr2w...@mail.gmail.com
---
src/backend/commands/tablecmds.c | 176 +++++++++++++++++++++-
src/test/regress/expected/constraints.out | 15 ++
src/test/regress/expected/indexing.out | 13 ++
src/test/regress/sql/constraints.sql | 11 ++
src/test/regress/sql/indexing.sql | 8 +
5 files changed, 220 insertions(+), 3 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 3be2e051d32..a3d1fe658c6 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -740,6 +740,7 @@ static List *GetParentedForeignKeyRefs(Relation partition);
static void ATDetachCheckNoForeignKeyRefs(Relation partition);
static char GetAttributeCompression(Oid atttypid, const char *compression);
static char GetAttributeStorage(Oid atttypid, const char *storagemode);
+static void recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, bool error_out);
/* ----------------------------------------------------------------
@@ -9333,6 +9334,26 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
ReleaseSysCache(tuple);
+ object.classId = RelationRelationId;
+ object.objectId = RelationGetRelid(rel);
+ object.objectSubId = attnum;
+
+ /*
+ * ALTER TABLE DROP COLUMN must also remove indexes or constraints that
+ * contain whole-row Var reference expressions. Since there is no direct
+ * dependency recorded between whole-row Vars and individual columns, and
+ * creating such dependencies would cause catalog bloat (see
+ * find_expr_references_walker).
+ *
+ * Here we handle this explicitly. We call recordWholeRowDependencyOnOrError
+ * to establish a dependency between the column and any constraint or index
+ * involving whole-row Vars. performMultipleDeletions will then take care
+ * of removing them.
+ */
+ recordWholeRowDependencyOnOrError(rel, &object, false);
+
+ CommandCounterIncrement();
+
/*
* Propagate to children as appropriate. Unlike most other ALTER
* routines, we have to do this one level of recursion at a time; we can't
@@ -9426,9 +9447,6 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
}
/* Add object to delete */
- object.classId = RelationRelationId;
- object.objectId = RelationGetRelid(rel);
- object.objectSubId = attnum;
add_exact_object_address(&object, addrs);
if (!recursing)
@@ -22062,3 +22080,155 @@ GetAttributeStorage(Oid atttypid, const char *storagemode)
return cstorage;
}
+
+/*
+ * Record dependencies between whole-row objects (indexes, CHECK constraints)
+ * associated with relation and relation's ObjectAddress.
+ *
+ * error_out means can not install such dependency, we have to error out explicitly.
+ */
+static void
+recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, bool error_out)
+{
+ Node *expr;
+ List *indexlist = NIL;
+ ObjectAddress idx_obj;
+ bool find_wholerow = false;
+ ScanKeyData skey[1];
+ TupleConstr *constr = RelationGetDescr(rel)->constr;
+
+ /* check CHECK constraints contain whole-row references or not */
+ if (constr && constr->num_check > 0)
+ {
+ Relation pg_constraint;
+ SysScanDesc conscan;
+ HeapTuple htup;
+ ObjectAddress con_obj;
+
+ pg_constraint = table_open(ConstraintRelationId, AccessShareLock);
+
+ /* Search pg_constraint for relevant entries */
+ ScanKeyInit(&skey[0],
+ 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);
+ Datum val;
+ bool isnull;
+ Bitmapset *expr_attrs = NULL;
+
+ if (conform->contype != CONSTRAINT_CHECK)
+ continue;
+
+ /* Grab and test conbin is actually set */
+ val = 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(val);
+
+ expr = stringToNode(s);
+ pfree(s);
+
+ /* Find all attributes referenced */
+ pull_varattnos(expr, 1, &expr_attrs);
+
+ find_wholerow = bms_is_member(0 - FirstLowInvalidHeapAttributeNumber,
+ expr_attrs);
+ if (find_wholerow && !error_out)
+ {
+ con_obj.classId = ConstraintRelationId;
+ con_obj.objectId = conform->oid;
+ con_obj.objectSubId = 0;
+
+ /* record dependency for constraints that references whole-row */
+ recordDependencyOn(&con_obj, object, DEPENDENCY_AUTO);
+ }
+ }
+ }
+ systable_endscan(conscan);
+ table_close(pg_constraint, AccessShareLock);
+ }
+
+ /* check indexes contain whole-row references or not */
+ find_wholerow = false;
+ indexlist = RelationGetIndexList(rel);
+ foreach_oid(indexoid, indexlist)
+ {
+ HeapTuple indexTuple;
+ Form_pg_index indexStruct;
+ Node *node;
+
+ indexTuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexoid));
+ if (!HeapTupleIsValid(indexTuple))
+ elog(ERROR, "cache lookup failed for index %u", indexoid);
+ indexStruct = (Form_pg_index) GETSTRUCT(indexTuple);
+
+ if (!heap_attisnull(indexTuple, Anum_pg_index_indexprs, NULL))
+ {
+ Datum exprDatum;
+ char *exprString;
+ Bitmapset *expr_attrs = NULL;
+
+ /* Convert text string to node tree */
+ exprDatum = SysCacheGetAttrNotNull(INDEXRELID, indexTuple,
+ Anum_pg_index_indexprs);
+ exprString = TextDatumGetCString(exprDatum);
+ node = (Node *) stringToNode(exprString);
+ pfree(exprString);
+
+ pull_varattnos(node, 1, &expr_attrs);
+
+ find_wholerow = bms_is_member(0 - FirstLowInvalidHeapAttributeNumber,
+ expr_attrs);
+ if (find_wholerow && !error_out)
+ {
+ idx_obj.classId = RelationRelationId;
+ idx_obj.objectId = indexStruct->indexrelid;
+ idx_obj.objectSubId = 0;
+ /* record dependency for indexes that references whole-row */
+ recordDependencyOn(&idx_obj, object, DEPENDENCY_AUTO);
+
+ ReleaseSysCache(indexTuple);
+ continue;
+ }
+ }
+
+ if (!heap_attisnull(indexTuple, Anum_pg_index_indpred, NULL))
+ {
+ Datum predDatum;
+ char *predString;
+ Bitmapset *expr_attrs = NULL;
+
+ /* Convert text string to node tree */
+ predDatum = SysCacheGetAttrNotNull(INDEXRELID, indexTuple,
+ Anum_pg_index_indpred);
+ predString = TextDatumGetCString(predDatum);
+ node = (Node *) stringToNode(predString);
+ pfree(predString);
+
+ pull_varattnos(node, 1, &expr_attrs);
+ find_wholerow = bms_is_member(0 - FirstLowInvalidHeapAttributeNumber,
+ expr_attrs);
+ if (find_wholerow && !error_out)
+ {
+ idx_obj.classId = RelationRelationId;
+ idx_obj.objectId = indexStruct->indexrelid;
+ idx_obj.objectSubId = 0;
+
+ /* record dependency for indexes that references whole-row */
+ recordDependencyOn(&idx_obj, object, DEPENDENCY_AUTO);
+ }
+ }
+ ReleaseSysCache(indexTuple);
+ }
+}
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 3590d3274f0..d68f5a13e44 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -254,6 +254,21 @@ ERROR: system column "ctid" reference in check constraint is invalid
LINE 3: CHECK (NOT (is_capital AND ctid::text = 'sys_col_check...
^
--
+-- Drop column also drop check constraints that have whole-row reference
+--
+CREATE TABLE DROP_COL_CHECK_TBL (
+ city TEXT, state TEXT,
+ CONSTRAINT cc0 CHECK (DROP_COL_CHECK_TBL is null) NOT ENFORCED,
+ CONSTRAINT cc1 CHECK (DROP_COL_CHECK_TBL is not null) NOT ENFORCED);
+ALTER TABLE DROP_COL_CHECK_TBL DROP COLUMN city;
+\d DROP_COL_CHECK_TBL
+ Table "public.drop_col_check_tbl"
+ Column | Type | Collation | Nullable | Default
+--------+------+-----------+----------+---------
+ state | text | | |
+
+DROP TABLE DROP_COL_CHECK_TBL;
+--
-- Check inheritance of defaults and constraints
--
CREATE TABLE INSERT_CHILD (cx INT default 42,
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index 4d29fb85293..40512a8853a 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -654,6 +654,19 @@ alter table idxpart2 drop column c;
b | integer | | |
drop table idxpart, idxpart2;
+create table idxpart (a int, b int, c int);
+create index on idxpart(c);
+create index idxpart_idx1 on idxpart((idxpart is not null));
+create index idxpart_idx2 on idxpart(a) where idxpart is not null;
+alter table idxpart drop column c;
+\d idxpart
+ Table "public.idxpart"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+
+drop table idxpart;
-- Verify that expression indexes inherit correctly
create table idxpart (a int, b int) partition by range (a);
create table idxpart1 (like idxpart);
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 1f6dc8fd69f..34de5b3fd89 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -165,6 +165,17 @@ CREATE TABLE SYS_COL_CHECK_TBL (city text, state text, is_capital bool,
altitude int,
CHECK (NOT (is_capital AND ctid::text = 'sys_col_check_tbl')));
+--
+-- Drop column also drop check constraints that have whole-row reference
+--
+CREATE TABLE DROP_COL_CHECK_TBL (
+ city TEXT, state TEXT,
+ CONSTRAINT cc0 CHECK (DROP_COL_CHECK_TBL is null) NOT ENFORCED,
+ CONSTRAINT cc1 CHECK (DROP_COL_CHECK_TBL is not null) NOT ENFORCED);
+ALTER TABLE DROP_COL_CHECK_TBL DROP COLUMN city;
+\d DROP_COL_CHECK_TBL
+DROP TABLE DROP_COL_CHECK_TBL;
+
--
-- Check inheritance of defaults and constraints
--
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql
index b5cb01c2d70..9604495c0ec 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -295,6 +295,14 @@ alter table idxpart2 drop column c;
\d idxpart2
drop table idxpart, idxpart2;
+create table idxpart (a int, b int, c int);
+create index on idxpart(c);
+create index idxpart_idx1 on idxpart((idxpart is not null));
+create index idxpart_idx2 on idxpart(a) where idxpart is not null;
+alter table idxpart drop column c;
+\d idxpart
+drop table idxpart;
+
-- Verify that expression indexes inherit correctly
create table idxpart (a int, b int) partition by range (a);
create table idxpart1 (like idxpart);
--
2.34.1
From 74ce2ccae810826bb080f014a546b0b011b86171 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Tue, 23 Sep 2025 13:33:30 +0800
Subject: [PATCH v5 2/3] disallow ALTER COLUMN SET DATA TYPE when wholerow
referenced constraint exists
CREATE TABLE ts (a int, c int, b int constraint cc check((ts = ROW(1,1,1))));
INSERT INTO ts values (1,1,1);
ALTER TABLE ts ALTER COLUMN b SET DATA TYPE INT8;
ERROR: cannot alter table "ts" because constraint "cc" uses its row type
HINT: You might need to drop constraint "cc" first
create index on ts((ts is not null));
alter table ts alter column a set data type int8; --error
ERROR: cannot alter table "ts" because index "ts_expr_idx" uses its row type
HINT: You might need to drop index "ts_expr_idx" first
discussion: https://postgr.es/m/cacjufxga6kvqy7dbhglvw9s9kkmpgyzt5me6c7kefjdpr2w...@mail.gmail.com
---
src/backend/commands/tablecmds.c | 41 +++++++++++++++++++++++
src/test/regress/expected/constraints.out | 11 ++++++
src/test/regress/expected/indexing.out | 9 +++++
src/test/regress/sql/constraints.sql | 10 ++++++
src/test/regress/sql/indexing.sql | 5 +++
5 files changed, 76 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index a3d1fe658c6..9bf5d6bc2b6 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -14541,6 +14541,20 @@ ATPrepAlterColumnType(List **wqueue,
find_composite_type_dependencies(rel->rd_rel->reltype, rel, NULL);
}
+ /*
+ * If the table has a whole-row referenced CHECK constraint, indexes, then
+ * changing column data type is not allowed.
+ */
+ if (targettype != attTup->atttypid || targettypmod != attTup->atttypmod)
+ {
+ ObjectAddress object;
+
+ object.classId = RelationRelationId;
+ object.objectId = RelationGetRelid(rel);
+ object.objectSubId = attnum;
+ recordWholeRowDependencyOnOrError(rel, &object, true);
+ }
+
ReleaseSysCache(tuple);
/*
@@ -22153,6 +22167,15 @@ recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, boo
/* record dependency for constraints that references whole-row */
recordDependencyOn(&con_obj, object, DEPENDENCY_AUTO);
}
+
+ if (find_wholerow && error_out)
+ ereport(ERROR,
+ errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("cannot alter table \"%s\" because constraint \"%s\" uses its row type",
+ RelationGetRelationName(rel),
+ NameStr(conform->conname)),
+ errhint("You might need to drop constraint \"%s\" first",
+ NameStr(conform->conname)));
}
}
systable_endscan(conscan);
@@ -22201,6 +22224,15 @@ recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, boo
ReleaseSysCache(indexTuple);
continue;
}
+
+ if (find_wholerow && error_out)
+ ereport(ERROR,
+ errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("cannot alter table \"%s\" because index \"%s\" uses its row type",
+ RelationGetRelationName(rel),
+ get_rel_name(indexStruct->indexrelid)),
+ errhint("You might need to drop index \"%s\" first",
+ get_rel_name(indexStruct->indexrelid)));
}
if (!heap_attisnull(indexTuple, Anum_pg_index_indpred, NULL))
@@ -22228,6 +22260,15 @@ recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, boo
/* record dependency for indexes that references whole-row */
recordDependencyOn(&idx_obj, object, DEPENDENCY_AUTO);
}
+
+ if (find_wholerow && error_out)
+ ereport(ERROR,
+ errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("cannot alter table \"%s\" because index \"%s\" uses its row type",
+ RelationGetRelationName(rel),
+ get_rel_name(indexStruct->indexrelid)),
+ errhint("You might need to drop index \"%s\" first",
+ get_rel_name(indexStruct->indexrelid)));
}
ReleaseSysCache(indexTuple);
}
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index d68f5a13e44..c0f6593956f 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -269,6 +269,17 @@ ALTER TABLE DROP_COL_CHECK_TBL DROP COLUMN city;
DROP TABLE DROP_COL_CHECK_TBL;
--
+-- Change column data type should error out because the whole-row referenced
+-- check constraint is still need it.
+--
+CREATE TABLE ALTER_COL_CHECK_TBL (
+ city INT, state TEXT,
+ CONSTRAINT cc1 CHECK (ALTER_COL_CHECK_TBL is not null) NOT ENFORCED);
+ALTER TABLE ALTER_COL_CHECK_TBL ALTER COLUMN city SET DATA TYPE int8;
+ERROR: cannot alter table "alter_col_check_tbl" because constraint "cc1" uses its row type
+HINT: You might need to drop constraint "cc1" first
+DROP TABLE ALTER_COL_CHECK_TBL;
+--
-- Check inheritance of defaults and constraints
--
CREATE TABLE INSERT_CHILD (cx INT default 42,
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index 40512a8853a..4b683c679a8 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -666,6 +666,15 @@ alter table idxpart drop column c;
a | integer | | |
b | integer | | |
+create index idxpart_idx1 on idxpart((idxpart is not null));
+create index idxpart_idx2 on idxpart(a) where idxpart is not null;
+alter table idxpart alter column a set data type int8; --error
+ERROR: cannot alter table "idxpart" because index "idxpart_idx1" uses its row type
+HINT: You might need to drop index "idxpart_idx1" first
+drop index idxpart_idx1;
+alter table idxpart alter column b set data type int8; --error
+ERROR: cannot alter table "idxpart" because index "idxpart_idx2" uses its row type
+HINT: You might need to drop index "idxpart_idx2" first
drop table idxpart;
-- Verify that expression indexes inherit correctly
create table idxpart (a int, b int) partition by range (a);
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 34de5b3fd89..18e120e7ce6 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -176,6 +176,16 @@ ALTER TABLE DROP_COL_CHECK_TBL DROP COLUMN city;
\d DROP_COL_CHECK_TBL
DROP TABLE DROP_COL_CHECK_TBL;
+--
+-- Change column data type should error out because the whole-row referenced
+-- check constraint is still need it.
+--
+CREATE TABLE ALTER_COL_CHECK_TBL (
+ city INT, state TEXT,
+ CONSTRAINT cc1 CHECK (ALTER_COL_CHECK_TBL is not null) NOT ENFORCED);
+ALTER TABLE ALTER_COL_CHECK_TBL ALTER COLUMN city SET DATA TYPE int8;
+DROP TABLE ALTER_COL_CHECK_TBL;
+
--
-- Check inheritance of defaults and constraints
--
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql
index 9604495c0ec..b7c08c9ff5a 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -301,6 +301,11 @@ create index idxpart_idx1 on idxpart((idxpart is not null));
create index idxpart_idx2 on idxpart(a) where idxpart is not null;
alter table idxpart drop column c;
\d idxpart
+create index idxpart_idx1 on idxpart((idxpart is not null));
+create index idxpart_idx2 on idxpart(a) where idxpart is not null;
+alter table idxpart alter column a set data type int8; --error
+drop index idxpart_idx1;
+alter table idxpart alter column b set data type int8; --error
drop table idxpart;
-- Verify that expression indexes inherit correctly
--
2.34.1
From 0390378c6d90c7e77cdce81e9af5b86f360354fb Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Tue, 23 Sep 2025 13:35:35 +0800
Subject: [PATCH v5 3/3] disallow change or drop column when wholerow
referenced policy exists
demo:
CREATE TABLE rls_tbl (a int, b int, c int);
CREATE POLICY p1 ON rls_tbl USING (rls_tbl >= ROW(1,1,1));
ALTER TABLE rls_tbl DROP COLUMN b; --error
ERROR: cannot drop column b of table rls_tbl because other objects depend on it
DETAIL: policy p1 on table rls_tbl depends on column b of table rls_tbl
HINT: Use DROP ... CASCADE to drop the dependent objects too.
ALTER TABLE rls_tbl ALTER COLUMN b SET DATA TYPE BIGINT; --error
ERROR: cannot alter table "rls_tbl" because security policy "p1" uses its row type
HINT: You might need to drop policy "p1" first
ALTER TABLE rls_tbl DROP COLUMN b CASCADE; --ok
NOTICE: drop cascades to policy p1 on table rls_tbl
ALTER TABLE
discussion: https://postgr.es/m/cacjufxga6kvqy7dbhglvw9s9kkmpgyzt5me6c7kefjdpr2w...@mail.gmail.com
---
src/backend/commands/tablecmds.c | 94 ++++++++++++++++++++++-
src/backend/optimizer/util/var.c | 60 +++++++++++++++
src/include/optimizer/optimizer.h | 1 +
src/test/regress/expected/rowsecurity.out | 27 +++++++
src/test/regress/sql/rowsecurity.sql | 17 ++++
src/tools/pgindent/typedefs.list | 1 +
6 files changed, 198 insertions(+), 2 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 9bf5d6bc2b6..797cba12ab9 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -22096,8 +22096,8 @@ GetAttributeStorage(Oid atttypid, const char *storagemode)
}
/*
- * Record dependencies between whole-row objects (indexes, CHECK constraints)
- * associated with relation and relation's ObjectAddress.
+ * Record dependencies between whole-row objects (indexes, CHECK constraints or
+ * policies) associated with relation and relation's ObjectAddress.
*
* error_out means can not install such dependency, we have to error out explicitly.
*/
@@ -22107,8 +22107,13 @@ recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, boo
Node *expr;
List *indexlist = NIL;
ObjectAddress idx_obj;
+ ObjectAddress pol_obj;
bool find_wholerow = false;
ScanKeyData skey[1];
+ Relation pg_policy;
+ SysScanDesc sscan;
+ HeapTuple policy_tuple;
+ Oid reltypid;
TupleConstr *constr = RelationGetDescr(rel)->constr;
/* check CHECK constraints contain whole-row references or not */
@@ -22272,4 +22277,89 @@ recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, boo
}
ReleaseSysCache(indexTuple);
}
+
+ /* Search pg_policy for whole-row references entries */
+ find_wholerow = false;
+ reltypid = get_rel_type_id(RelationGetRelid(rel));
+
+ pg_policy = table_open(PolicyRelationId, AccessShareLock);
+
+ ScanKeyInit(&skey[0],
+ Anum_pg_policy_polrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ sscan = systable_beginscan(pg_policy,
+ PolicyPolrelidPolnameIndexId, true, NULL, 1,
+ skey);
+ while (HeapTupleIsValid(policy_tuple = systable_getnext(sscan)))
+ {
+ Datum datum;
+ bool isnull;
+ char *str_value;
+ Node *polexpr;
+
+ Form_pg_policy policy = (Form_pg_policy) GETSTRUCT(policy_tuple);
+
+ /* Get policy qual */
+ datum = heap_getattr(policy_tuple, Anum_pg_policy_polqual,
+ RelationGetDescr(pg_policy), &isnull);
+ if (!isnull)
+ {
+ str_value = TextDatumGetCString(datum);
+ polexpr = (Node *) stringToNode(str_value);
+ pfree(str_value);
+
+ find_wholerow = ExprContainWholeRow(polexpr, reltypid);
+ if (find_wholerow && !error_out)
+ {
+ pol_obj.classId = PolicyRelationId;
+ pol_obj.objectId = policy->oid;
+ pol_obj.objectSubId = 0;
+
+ /* record dependency for policies that references whole-row Var */
+ recordDependencyOn(&pol_obj, object, DEPENDENCY_NORMAL);
+ continue;
+ }
+
+ if (find_wholerow && error_out)
+ ereport(ERROR,
+ errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("cannot alter table \"%s\" because security policy \"%s\" uses its row type",
+ RelationGetRelationName(rel),
+ NameStr(policy->polname)),
+ errhint("You might need to drop security policy \"%s\" first",
+ NameStr(policy->polname)));
+ }
+
+ datum = heap_getattr(policy_tuple, Anum_pg_policy_polwithcheck,
+ RelationGetDescr(pg_policy), &isnull);
+ if (!isnull)
+ {
+ str_value = TextDatumGetCString(datum);
+ polexpr = (Node *) stringToNode(str_value);
+ pfree(str_value);
+
+ find_wholerow = ExprContainWholeRow(polexpr, reltypid);
+ if (find_wholerow && !error_out)
+ {
+ pol_obj.classId = PolicyRelationId;
+ pol_obj.objectId = policy->oid;
+ pol_obj.objectSubId = 0;
+
+ /* record dependency for policies that references whole-row Var */
+ recordDependencyOn(&pol_obj, object, DEPENDENCY_NORMAL);
+ }
+
+ if (find_wholerow && error_out)
+ ereport(ERROR,
+ errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("cannot alter table \"%s\" because security policy \"%s\" uses its row type",
+ RelationGetRelationName(rel),
+ NameStr(policy->polname)),
+ errhint("You might need to drop security policy \"%s\" first",
+ NameStr(policy->polname)));
+ }
+ }
+ systable_endscan(sscan);
+ table_close(pg_policy, AccessShareLock);
}
diff --git a/src/backend/optimizer/util/var.c b/src/backend/optimizer/util/var.c
index 8065237a189..e5e0c4edde5 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,60 @@ 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))
+ {
+ bool result;
+
+ result = query_tree_walker((Query *) node, ExprContainWholeRow_walker,
+ context, 0);
+ return result;
+ }
+
+ return expression_tree_walker(node, ExprContainWholeRow_walker, context);
+}
+
+
+/*
+ * ExprContainWholeRow -
+ *
+ * Determine whether an expression contains a whole-row Var, 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 04878f1f1c2..69d5e6905da 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -194,6 +194,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/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 8c879509313..f3be08e9743 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -2357,6 +2357,33 @@ SELECT * FROM document;
14 | 11 | 1 | regress_rls_bob | new novel |
(16 rows)
+--check drop column (no CASCADE) or alter column data type will fail because of
+--whole-row referenced security policy exists.
+ALTER TABLE document ADD COLUMN dummy INT4, ADD COLUMN dummy1 INT4;
+CREATE POLICY p7 ON document AS PERMISSIVE
+ USING (cid IS NOT NULL AND
+ (WITH cte AS (SELECT TRUE FROM uaccount
+ WHERE EXISTS (SELECT document FROM uaccount WHERE uaccount IS NULL))
+ SELECT * FROM cte));
+ALTER TABLE document ALTER COLUMN dummy SET DATA TYPE BIGINT; --error
+ERROR: cannot alter table "document" because security policy "p7" uses its row type
+HINT: You might need to drop security policy "p7" first
+ALTER TABLE document DROP COLUMN dummy; --error
+ERROR: cannot drop column dummy of table document because other objects depend on it
+DETAIL: policy p7 on table document depends on column dummy of table document
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+ALTER TABLE document DROP COLUMN dummy CASCADE; --ok
+NOTICE: drop cascades to policy p7 on table document
+CREATE POLICY p8 ON document FOR INSERT WITH CHECK (document IS NOT NULL);
+ALTER TABLE document ALTER COLUMN dummy1 SET DATA TYPE BIGINT; --error
+ERROR: cannot alter table "document" because security policy "p8" uses its row type
+HINT: You might need to drop security policy "p8" first
+ALTER TABLE document DROP COLUMN dummy1; --error
+ERROR: cannot drop column dummy1 of table document because other objects depend on it
+DETAIL: policy p8 on table document depends on column dummy1 of table document
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+ALTER TABLE document DROP COLUMN dummy1 CASCADE; --ok
+NOTICE: drop cascades to policy p8 on table document
--
-- ROLE/GROUP
--
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 21ac0ca51ee..ead0a5e62be 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -1021,6 +1021,23 @@ DROP POLICY p1 ON document;
-- Just check everything went per plan
SELECT * FROM document;
+--check drop column (no CASCADE) or alter column data type will fail because of
+--whole-row referenced security policy exists.
+ALTER TABLE document ADD COLUMN dummy INT4, ADD COLUMN dummy1 INT4;
+CREATE POLICY p7 ON document AS PERMISSIVE
+ USING (cid IS NOT NULL AND
+ (WITH cte AS (SELECT TRUE FROM uaccount
+ WHERE EXISTS (SELECT document FROM uaccount WHERE uaccount IS NULL))
+ SELECT * FROM cte));
+ALTER TABLE document ALTER COLUMN dummy SET DATA TYPE BIGINT; --error
+ALTER TABLE document DROP COLUMN dummy; --error
+ALTER TABLE document DROP COLUMN dummy CASCADE; --ok
+
+CREATE POLICY p8 ON document FOR INSERT WITH CHECK (document IS NOT NULL);
+ALTER TABLE document ALTER COLUMN dummy1 SET DATA TYPE BIGINT; --error
+ALTER TABLE document DROP COLUMN dummy1; --error
+ALTER TABLE document DROP COLUMN dummy1 CASCADE; --ok
+
--
-- ROLE/GROUP
--
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index e90af5b2ad3..3db0a0beed1 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3521,6 +3521,7 @@ conn_oauth_scope_func
conn_sasl_state_func
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
