public inbox for [email protected]
help / color / mirror / Atom feedRe: let ALTER TABLE DROP COLUMN drop whole-row referenced object
4+ messages / 3 participants
[nested] [flat]
* Re: let ALTER TABLE DROP COLUMN drop whole-row referenced object
@ 2026-01-19 09:59 =?GBK?B?vfAg?= <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: =?GBK?B?vfAg?= @ 2026-01-19 09:59 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: Chao Li <[email protected]>; pgsql-hackers
At 2026-01-19 17:09:54, "jian he" <[email protected]> wrote:
>hi.
>
>CREATE FUNCTION dummy_trigger() RETURNS TRIGGER AS $$
> BEGIN
> RETURN NULL;
> END
>$$ language plpgsql;
>
>create table main_table(a int);
>CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
>FOR EACH ROW
>WHEN (new.a > 0)
>EXECUTE PROCEDURE dummy_trigger();
>
>ALTER TABLE main_table ALTER COLUMN a SET DATA TYPE INT8; --error
>ALTER TABLE main_table DROP COLUMN a; --error
>
>Dropping a column or changing its data type will fail if the column is
>referenced in a trigger’s WHEN clause, that's the current behavior.
>I think we should expand that to a whole-row reference WHEN clause in trigger.
>
>DROP TRIGGER before_ins_stmt_trig ON main_table;
>CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
>FOR EACH ROW
>WHEN (new is null)
>EXECUTE PROCEDURE dummy_trigger();
>ALTER TABLE main_table ALTER COLUMN a SET DATA TYPE INT8; --expect to error
>ALTER TABLE main_table DROP COLUMN a; --expect to error
>
>new summary:
>For (constraints, indexes, policies, triggers) that contain whole-row
>references:
>ALTER TABLE DROP COLUMN [CASCADE] will drop these objects too.
>
>ALTER COLUMN SET DATA TYPE will error out because whole-row–dependent objects
>exist.
>
>
Hello,
I did take a look at v7-0001 and v7-0002.
In v7-0002:
1.
> + pull_varattnos(expr, PRS2_OLD_VARNO, &expr_attrs);
> + pull_varattnos(expr, PRS2_NEW_VARNO, &expr_attrs);
The function "pull_varattnos" was called twice, with different varno parameters each time (first using PRS2_NEW_VARNO, then using PRS2_OLD_VARNO).
I think it would be better to use PRS2_NEW_VARNO | PRS2_OLD_VARNO in a single call.
pull_varattnos(expr, PRS2_NEW_VARNO | PRS2_OLD_VARNO, &expr_attrs);
2.
> + if (trig->tgqual != NULL)
Should we first check if TRIGGER_FOR_ROW(trig->tgtype) before processing trig->tgqual are more appropriate?
3.
Another issue is the Bitmapset set allocated by pull_varattnos will never be released. This will cause a memory leak.
Please add the statement bms_free(expr_attrs); after each usage within the loops in recordWholeRowDependencyOnOrError.
In v7-0001：
As in v7-0002, the Bitmapset returned by pull_varattnos is never released.
Please add the statement bms_free(expr_attrs);
Regards,
Jinbinge
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: let ALTER TABLE DROP COLUMN drop whole-row referenced object
@ 2026-02-28 07:27 jian he <[email protected]>
parent: =?GBK?B?vfAg?= <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: jian he @ 2026-02-28 07:27 UTC (permalink / raw)
To: 金 <[email protected]>; Kirill Reshke <[email protected]>; +Cc: Chao Li <[email protected]>; pgsql-hackers
On Mon, Jan 19, 2026 at 2:56 AM Kirill Reshke <[email protected]> wrote:
>
> Hi!
> I did take a look at v7.
>
> In 0001:
>
> > + indscan = systable_beginscan(pg_index, IndexIndrelidIndexId, true,
> > + NULL, 1, &skey);
> > + while (HeapTupleIsValid(htup = systable_getnext(indscan)))
> > + {
> > + Form_pg_index index = (Form_pg_index) GETSTRUCT(htup);
> > +
> > + /* add index's OID to result list */
> > + indexlist = lappend_oid(indexlist, index->indexrelid);
> > + }
> > + systable_endscan(indscan);
> > +
> > + table_close(pg_index, AccessShareLock);
> > +
> > + foreach_oid(indexoid, indexlist)
> > + {
>
> Hmm, why is this not just a one cycle? Also, not sure how many
> relations can be returned by pg_index scan. Maybe it is worth adding
> CHECK_FOR_INTERRUPTS() here?
>
I refactored this part, I realized that v7 has some duplicated code in
recordWholeRowDependencyOnOrError.
After refactoring, CHECK_FOR_INTERRUPTS is not needed, I think.
On Mon, Jan 19, 2026 at 6:00 PM 金 <[email protected]> wrote:
>
> Hello,
>
> I did take a look at v7-0001 and v7-0002.
>
> In v7-0002:
>
> 1.
> > + pull_varattnos(expr, PRS2_OLD_VARNO, &expr_attrs);
> > + pull_varattnos(expr, PRS2_NEW_VARNO, &expr_attrs);
>
> The function "pull_varattnos" was called twice, with different varno parameters each time (first using PRS2_NEW_VARNO, then using PRS2_OLD_VARNO).
> I think it would be better to use PRS2_NEW_VARNO | PRS2_OLD_VARNO in a single call.
> pull_varattnos(expr, PRS2_NEW_VARNO | PRS2_OLD_VARNO, &expr_attrs);
>
We are checking if the trig->tgqual expression referenced Var->varno
equals PRS2_NEW_VARNO or PRS2_NEW_VARNO.
PRS2_NEW_VARNO | PRS2_OLD_VARNO is equal to 3,
So, I don't think it will work, see pull_varattnos_walker.
> 2.
>
> > + if (trig->tgqual != NULL)
>
> Should we first check if TRIGGER_FOR_ROW(trig->tgtype) before processing trig->tgqual are more appropriate?
I see.
However, unconditionally checking the trigger's WHEN clause whole-row
references seems better, IMHO.
> 3.
> Another issue is the Bitmapset set allocated by pull_varattnos will never be released. This will cause a memory leak.
> Please add the statement bms_free(expr_attrs); after each usage within the loops in recordWholeRowDependencyOnOrError.
>
> In v7-0001：
>
> As in v7-0002, the Bitmapset returned by pull_varattnos is never released.
> Please add the statement bms_free(expr_attrs);
>
Yech, In this case, we need ``bms_free(expr_attrs);`` because in the function
recordWholeRowDependencyOnOrError, we use the variable `expr_attrs` constantly.
--
jian
https://www.enterprisedb.com/
Attachments:
[text/x-patch] v8-0002-disallow-drop-or-change-column-if-wholerow-trigger-exists.patch (8.1K, 2-v8-0002-disallow-drop-or-change-column-if-wholerow-trigger-exists.patch)
download | inline diff:
From 0304acea80d0f336c47c8d1f38d71602bf08f4da Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Sat, 28 Feb 2026 15:10:16 +0800
Subject: [PATCH v8 2/3] disallow drop or change column if wholerow trigger
exists
ALTER TABLE DROP COLUMN will fail if any trigger WHEN clause have whole-row reference.
In the recordWholeRowDependencyOnOrError function, we record a dependency
between the relation and the whole-row-referenced trigger. later
performMultipleDeletions will do the deleation.
ALTER COLUMN SET DATA TYPE fundamentally changes the table's record type. At
present, records containing columns of different data types cannot be compared
(see record_eq). Therefore ALTER COLUMN SET DATA TYPE should errr out in this
case, otherwise any trigger WHEN clause that compares whole-row values may
always evaluate to erorr out.
discussion: https://postgr.es/m/CACJufxGA6KVQy7DbHGLVw9s9KKmpGyZt5ME6C7kEfjDpr2wZCw@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/6055
---
src/backend/commands/tablecmds.c | 35 ++++++++++++++++++++++
src/test/regress/expected/foreign_data.out | 13 ++++++++
src/test/regress/expected/triggers.out | 27 +++++++++++++++++
src/test/regress/sql/foreign_data.sql | 9 ++++++
src/test/regress/sql/triggers.sql | 17 +++++++++++
5 files changed, 101 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index da43e9efed4..de9ad4ab195 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -23551,4 +23551,39 @@ recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object,
systable_endscan(indscan);
table_close(pg_index, AccessShareLock);
+
+ /* Now checking trigger whole-row references */
+ if (rel->trigdesc != NULL)
+ {
+ for (int i = 0; i < rel->trigdesc->numtriggers; i++)
+ {
+ Trigger *trig = &rel->trigdesc->triggers[i];
+
+ if (trig->tgqual == NULL)
+ continue;
+
+ expr = stringToNode(trig->tgqual);
+
+ pull_varattnos(expr, PRS2_OLD_VARNO, &expr_attrs);
+
+ pull_varattnos(expr, PRS2_NEW_VARNO, &expr_attrs);
+
+ have_wholerow = bms_is_member(0 - FirstLowInvalidHeapAttributeNumber,
+ expr_attrs);
+ bms_free(expr_attrs);
+ expr_attrs = NULL;
+
+ if (have_wholerow)
+ {
+ ObjectAddress trig_obj;
+
+ trig_obj.classId = TriggerRelationId;
+ trig_obj.objectId = trig->tgoid;
+ trig_obj.objectSubId = 0;
+
+ recordDependencyOnOrError(rel, &trig_obj, object, error_out,
+ DEPENDENCY_NORMAL);
+ }
+ }
+ }
}
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index cce49e509ab..033aefbb64f 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -1398,6 +1398,19 @@ DROP TRIGGER trigtest_before_stmt ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_after_stmt ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1;
+CREATE TRIGGER trigtest_before_stmt BEFORE INSERT OR UPDATE
+ON foreign_schema.foreign_table_1
+FOR EACH ROW
+WHEN (new IS NOT NULL)
+EXECUTE PROCEDURE dummy_trigger();
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 ALTER COLUMN c7 SET DATA TYPE bigint; -- error
+ERROR: cannot alter table "foreign_table_1" because trigger trigtest_before_stmt on foreign table foreign_schema.foreign_table_1 uses its row type
+HINT: You might need to drop trigger trigtest_before_stmt on foreign table foreign_schema.foreign_table_1 first
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 DROP COLUMN c7; -- error
+ERROR: cannot drop column c7 of foreign table foreign_schema.foreign_table_1 because other objects depend on it
+DETAIL: trigger trigtest_before_stmt on foreign table foreign_schema.foreign_table_1 depends on column c7 of foreign table foreign_schema.foreign_table_1
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+DROP TRIGGER trigtest_before_stmt ON foreign_schema.foreign_table_1;
DROP FUNCTION dummy_trigger();
-- Table inheritance
CREATE TABLE fd_pt1 (
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 98dee63b50a..6d202dccaff 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -227,6 +227,33 @@ ERROR: trigger "no_such_trigger" for table "main_table" does not exist
COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS 'right';
COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS NULL;
--
+-- test triggers with WHEN clause contain wholerow reference
+--
+CREATE TABLE test_tbl1 (a int, b int) PARTITION BY RANGE (a);
+CREATE TABLE test_tbl1p1 PARTITION OF test_tbl1 FOR VALUES FROM (0) TO (1000);
+CREATE TRIGGER test_tbl1p1_trig
+ BEFORE INSERT OR UPDATE ON test_tbl1p1 FOR EACH ROW
+ WHEN (new = ROW (1, 1))
+ EXECUTE PROCEDURE trigger_func ('test_tbl1p1');
+ALTER TABLE test_tbl1 ALTER COLUMN b SET DATA TYPE bigint; -- error
+ERROR: cannot alter table "test_tbl1p1" because trigger test_tbl1p1_trig on table test_tbl1p1 uses its row type
+HINT: You might need to drop trigger test_tbl1p1_trig on table test_tbl1p1 first
+ALTER TABLE test_tbl1 DROP COLUMN b; -- error
+ERROR: cannot drop desired object(s) because other objects depend on them
+DETAIL: trigger test_tbl1p1_trig on table test_tbl1p1 depends on column b of table test_tbl1p1
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+ALTER TABLE test_tbl1 DROP COLUMN b CASCADE; -- ok
+NOTICE: drop cascades to trigger test_tbl1p1_trig on table test_tbl1p1
+\d+ test_tbl1
+ Partitioned table "public.test_tbl1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+Partition key: RANGE (a)
+Partitions: test_tbl1p1 FOR VALUES FROM (0) TO (1000)
+
+DROP TABLE test_tbl1;
+--
-- test triggers with WHEN clause
--
CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table
diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql
index aa147b14a90..daa7bc0df0f 100644
--- a/src/test/regress/sql/foreign_data.sql
+++ b/src/test/regress/sql/foreign_data.sql
@@ -630,6 +630,15 @@ DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_after_stmt ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1;
+CREATE TRIGGER trigtest_before_stmt BEFORE INSERT OR UPDATE
+ON foreign_schema.foreign_table_1
+FOR EACH ROW
+WHEN (new IS NOT NULL)
+EXECUTE PROCEDURE dummy_trigger();
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 ALTER COLUMN c7 SET DATA TYPE bigint; -- error
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 DROP COLUMN c7; -- error
+DROP TRIGGER trigtest_before_stmt ON foreign_schema.foreign_table_1;
+
DROP FUNCTION dummy_trigger();
-- Table inheritance
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index ea39817ee3d..c2200d6aa96 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -158,6 +158,23 @@ COMMENT ON TRIGGER no_such_trigger ON main_table IS 'wrong';
COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS 'right';
COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS NULL;
+--
+-- test triggers with WHEN clause contain wholerow reference
+--
+CREATE TABLE test_tbl1 (a int, b int) PARTITION BY RANGE (a);
+CREATE TABLE test_tbl1p1 PARTITION OF test_tbl1 FOR VALUES FROM (0) TO (1000);
+
+CREATE TRIGGER test_tbl1p1_trig
+ BEFORE INSERT OR UPDATE ON test_tbl1p1 FOR EACH ROW
+ WHEN (new = ROW (1, 1))
+ EXECUTE PROCEDURE trigger_func ('test_tbl1p1');
+
+ALTER TABLE test_tbl1 ALTER COLUMN b SET DATA TYPE bigint; -- error
+ALTER TABLE test_tbl1 DROP COLUMN b; -- error
+ALTER TABLE test_tbl1 DROP COLUMN b CASCADE; -- ok
+\d+ test_tbl1
+DROP TABLE test_tbl1;
+
--
-- test triggers with WHEN clause
--
--
2.34.1
[text/x-patch] v8-0003-disallow-ALTER-TABLE-ALTER-COLUMN-when-wholerow-referenced-policy.patch (13.6K, 3-v8-0003-disallow-ALTER-TABLE-ALTER-COLUMN-when-wholerow-referenced-policy.patch)
download | inline diff:
From 4cbb54486832780a1454065879ba1957a3a78bee Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Sat, 28 Feb 2026 15:12:12 +0800
Subject: [PATCH v8 3/3] disallow ALTER TABLE ALTER COLUMN when wholerow
referenced policy exists
Policy have a DEPENDENCY_NORMAL type with their source table. Policy's qual and
with check qual are quite unconstrained (allowing subqueries), we can't reliably
use pull_varattnos to detect if they contain subqueries. A further complication
is that the qual and with check qual whole-row Var may not only references their
own table but also for other unrelated tables.
Therefore We should check pg_depend, not pg_policy, to see if dropping this
table affects any policy objects. After collecting the policies impacted by the
ALTER TABLE command, check each policy qual and with check qual, see if
whole-row references or not.
demo:
CREATE TABLE rls_tbl (a int, b int, c int);
CREATE TABLE t (a int);
CREATE POLICY p1 ON rls_tbl USING (rls_tbl >= ROW(1,1,1) and (select t is null from t));
ALTER TABLE t DROP COLUMN a; --error
ERROR: cannot drop column a of table t because other objects depend on it
DETAIL: policy p1 on table rls_tbl depends on column a of table t
HINT: Use DROP ... CASCADE to drop the dependent objects too.
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 t ALTER COLUMN a SET DATA TYPE BIGINT; --error
ERROR: cannot alter table "t" because security policy "p1" uses its row type
HINT: You might need to drop security policy "p1" first
discussion: https://postgr.es/m/CACJufxGA6KVQy7DbHGLVw9s9KKmpGyZt5ME6C7kEfjDpr2wZCw@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/6055
---
src/backend/commands/tablecmds.c | 123 ++++++++++++++++++++++
src/backend/optimizer/util/var.c | 54 ++++++++++
src/include/optimizer/optimizer.h | 1 +
src/test/regress/expected/rowsecurity.out | 29 ++++-
src/test/regress/sql/rowsecurity.sql | 17 +++
src/tools/pgindent/typedefs.list | 1 +
6 files changed, 223 insertions(+), 2 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index de9ad4ab195..4b453bbca03 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -749,6 +749,8 @@ static void recordWholeRowDependencyOnOrError(Relation rel,
const ObjectAddress *object,
bool error_out);
+static List *GetRelPolicies(Relation rel);
+
/* ----------------------------------------------------------------
* DefineRelation
* Creates a new relation.
@@ -23404,6 +23406,9 @@ recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object,
bool have_wholerow = false;
List *wholerow_idxoids = NIL;
TupleConstr *constr = RelationGetDescr(rel)->constr;
+ List *pols = NIL;
+ Relation pg_policy;
+ Oid reltypid;
/*
* Loop through each CHECK constraint, see if it contain whole-row
@@ -23586,4 +23591,122 @@ recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object,
}
}
}
+
+ /* Now checking policy whole-row references */
+ reltypid = get_rel_type_id(RelationGetRelid(rel));
+
+ pg_policy = table_open(PolicyRelationId, AccessShareLock);
+
+ pols = GetRelPolicies(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;
+
+ recordDependencyOnOrError(rel, &pol_obj, object, error_out,
+ DEPENDENCY_NORMAL);
+
+ continue;
+ }
+ }
+
+ 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;
+
+ recordDependencyOnOrError(rel, &pol_obj, object, error_out,
+ DEPENDENCY_NORMAL);
+ }
+ }
+ }
+ systable_endscan(sscan);
+ }
+ table_close(pg_policy, AccessShareLock);
+}
+
+static List *
+GetRelPolicies(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 66f5b598f0c..99cfe64bf8b 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 3d27a019609..dadf4b1d6c7 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -197,6 +197,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 07d93e7def1..4b3e6778fd3 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -2730,6 +2730,32 @@ 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.
+DROP TABLE part_document;
+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 uaccount ALTER COLUMN seclv SET DATA TYPE BIGINT; -- error
+ERROR: cannot alter table "uaccount" because policy p7 on table document uses its row type
+HINT: You might need to drop policy p7 on table document first
+ALTER TABLE document ALTER COLUMN dummy SET DATA TYPE BIGINT; -- error
+ERROR: cannot alter table "document" because policy p7 on table document uses its row type
+HINT: You might need to drop policy p7 on table document 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 uaccount DROP COLUMN seclv; -- error
+ERROR: cannot drop column seclv of table uaccount because other objects depend on it
+DETAIL: policy p7 on table document depends on column seclv of table uaccount
+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
+ALTER TABLE uaccount DROP COLUMN seclv CASCADE; -- ok
--
-- ROLE/GROUP
--
@@ -5198,12 +5224,11 @@ drop table rls_t, test_t;
--
RESET SESSION AUTHORIZATION;
DROP SCHEMA regress_rls_schema CASCADE;
-NOTICE: drop cascades to 30 other objects
+NOTICE: drop cascades to 29 other objects
DETAIL: drop cascades to function f_leak(text)
drop cascades to table uaccount
drop cascades to table category
drop cascades to table document
-drop cascades to table part_document
drop cascades to table dependent
drop cascades to table rec1
drop cascades to table rec2
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 6b3566271df..6445fb0f883 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -1219,6 +1219,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.
+DROP TABLE part_document;
+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 uaccount ALTER COLUMN seclv SET DATA TYPE BIGINT; -- error
+ALTER TABLE document ALTER COLUMN dummy SET DATA TYPE BIGINT; -- error
+
+ALTER TABLE document DROP COLUMN dummy; -- error
+ALTER TABLE uaccount DROP COLUMN seclv; -- error
+ALTER TABLE document DROP COLUMN dummy CASCADE; -- ok
+ALTER TABLE uaccount DROP COLUMN seclv CASCADE; -- ok
+
--
-- ROLE/GROUP
--
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 77e3c04144e..3bbe8dadf35 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3592,6 +3592,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
[text/x-patch] v8-0001-fix-DDL-wholerow-referenced-constraints-and-indexes.patch (15.9K, 4-v8-0001-fix-DDL-wholerow-referenced-constraints-and-indexes.patch)
download | inline diff:
From 1ec12f88281f4fb669cf9d20f6d36b5290a9913d Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Sat, 28 Feb 2026 15:07:21 +0800
Subject: [PATCH v8 1/3] fix DDL wholerow referenced constraints and indexes
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
1. ALTER TABLE DROP COLUMN.
ALTER TABLE DROP COLUMN will remove indexes or constraints contain whole-row
expression.
We enumerate all constraints and indexes and check one by one to determine
whether it contains a whole-row Var reference. If such a reference is found, we
record the dependency and later let performMultipleDeletions do the deleation.
for example:
CREATE TABLE ts (a int, constraint cc check((ts = ROW(1))));
CREATE INDEX tsi3 on ts ((ts is null));
ALTER TABLE DROP COLUMN should drop above all indexes, constraints on table ts.
2. ALTER COLUMN SET DATA TYPE
ALTER COLUMN SET DATA TYPE fundamentally changes the table’s record type; At
present, we cannot compare records that contain columns of dissimilar types, see
function record_eq. As a result, ALTER COLUMN SET DATA TYPE does not work for
whole-row reference objects (such as constraints and indexes), and must
therefore raise an error.
For example, below ALTER COLUMN SET DATA TYPE should fail.
CREATE TABLE ts (a int, CONSTRAINT cc CHECK ((ts = ROW(1))));
CREATE INDEX ON ts ((ts IS NOT NULL));
ALTER TABLE ts ALTER COLUMN a SET DATA TYPE int8;
discussion: https://postgr.es/m/CACJufxGA6KVQy7DbHGLVw9s9KKmpGyZt5ME6C7kEfjDpr2wZCw@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/6055
---
src/backend/commands/tablecmds.c | 221 +++++++++++++++++++++-
src/test/regress/expected/constraints.out | 21 ++
src/test/regress/expected/indexing.out | 35 ++++
src/test/regress/sql/constraints.sql | 16 ++
src/test/regress/sql/indexing.sql | 20 ++
5 files changed, 310 insertions(+), 3 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index b04b0dbd2a0..da43e9efed4 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -745,6 +745,9 @@ static void ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation
static void ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab,
Relation rel, PartitionCmd *cmd,
AlterTableUtilityContext *context);
+static void recordWholeRowDependencyOnOrError(Relation rel,
+ const ObjectAddress *object,
+ bool error_out);
/* ----------------------------------------------------------------
* DefineRelation
@@ -9391,6 +9394,21 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
ReleaseSysCache(tuple);
+ object.classId = RelationRelationId;
+ object.objectId = RelationGetRelid(rel);
+ object.objectSubId = attnum;
+
+ /*
+ * We also need remove indexes or constraints that contain whole-row Var
+ * reference. Using recordWholeRowDependencyOnOrError to establish a
+ * dependency between the column and any CHECK constraint or index
+ * contains whole-row Vars reference. Function performMultipleDeletions
+ * will then take care of removing them later.
+ */
+ 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
@@ -9484,9 +9502,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)
@@ -14772,6 +14787,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
SysScanDesc scan;
HeapTuple depTup;
ObjectAddress address;
+ ObjectAddress object;
/*
* Clear all the missing values if we're rewriting the table, since this
@@ -14867,6 +14883,16 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
*/
RememberAllDependentForRebuilding(tab, AT_AlterColumnType, rel, attnum, colName);
+ object.classId = RelationRelationId;
+ object.objectId = RelationGetRelid(rel);
+ object.objectSubId = attnum;
+
+ /*
+ * Check for whole-row referenced objects (constraints, indexes etc) --
+ * can't cope
+ */
+ recordWholeRowDependencyOnOrError(rel, &object, true);
+
/*
* Now scan for dependencies of this column on other things. The only
* things we should find are the dependency on the column datatype and
@@ -23337,3 +23363,192 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* Restore the userid and security context. */
SetUserIdAndSecContext(save_userid, save_sec_context);
}
+
+static void
+recordDependencyOnOrError(Relation rel, const ObjectAddress *depender,
+ const ObjectAddress *referenced, bool error_out,
+ DependencyType behavior)
+{
+ if (error_out)
+ ereport(ERROR,
+ errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("cannot alter table \"%s\" because %s uses its row type",
+ RelationGetRelationName(rel),
+ getObjectDescription(depender, false)),
+ errhint("You might need to drop %s first",
+ getObjectDescription(depender, false)));
+ else
+ recordDependencyOn(depender, referenced, behavior);
+}
+
+/*
+ * Record dependencies between whole-row Var referenced objects (indexes, CHECK
+ * constraints, etc) and the relation's ObjectAddress.
+ *
+ * error_out means can not install such dependency, we need error out explicitly.
+ */
+static void
+recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object,
+ bool error_out)
+{
+ 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;
+ TupleConstr *constr = RelationGetDescr(rel)->constr;
+
+ /*
+ * 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;
+ ObjectAddress con_obj;
+
+ 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)
+ {
+ con_obj.classId = ConstraintRelationId;
+ con_obj.objectId = conform->oid;
+ con_obj.objectSubId = 0;
+
+ recordDependencyOnOrError(rel, &con_obj, object, error_out,
+ DEPENDENCY_AUTO);
+ }
+ }
+ }
+ 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
+ */
+ 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)))
+ {
+ ObjectAddress idx_obj;
+ 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)
+ {
+ idx_obj.classId = RelationRelationId;
+ idx_obj.objectId = index->indexrelid;
+ idx_obj.objectSubId = 0;
+
+ wholerow_idxoids = lappend_oid(wholerow_idxoids, index->indexrelid);
+
+ recordDependencyOnOrError(rel, &idx_obj, object, error_out,
+ DEPENDENCY_AUTO);
+
+ 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)
+ {
+ idx_obj.classId = RelationRelationId;
+ idx_obj.objectId = index->indexrelid;
+ idx_obj.objectSubId = 0;
+
+ wholerow_idxoids = lappend_oid(wholerow_idxoids, index->indexrelid);
+
+ recordDependencyOnOrError(rel, &idx_obj, object, error_out,
+ DEPENDENCY_AUTO);
+ }
+ }
+ }
+ systable_endscan(indscan);
+
+ table_close(pg_index, AccessShareLock);
+}
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index a6fa9cacb72..39600331b35 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -254,6 +254,27 @@ 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 all check constraints that have whole-row references.
+--
+-- Change column data type should error out because the whole-row referenced
+-- check constraint is still needed.
+-- no need to worry about partitioned tables, since the whole-row check constraint
+-- can not span multi relation.
+CREATE TABLE wholerow_check_tbl (
+ city int,
+ CONSTRAINT cc0 CHECK (wholerow_check_tbl is null) NOT ENFORCED,
+ CONSTRAINT cc1 CHECK (wholerow_check_tbl is not null) NOT ENFORCED);
+ALTER TABLE wholerow_check_tbl ALTER COLUMN city SET DATA TYPE INT8;
+ERROR: cannot alter table "wholerow_check_tbl" because constraint cc0 on table wholerow_check_tbl uses its row type
+HINT: You might need to drop constraint cc0 on table wholerow_check_tbl first
+ALTER TABLE wholerow_check_tbl DROP COLUMN city; -- ok
+\d wholerow_check_tbl
+ Table "public.wholerow_check_tbl"
+ Column | Type | Collation | Nullable | Default
+--------+------+-----------+----------+---------
+
+DROP TABLE wholerow_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 dc629928c8f..b76c9ffaefb 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -654,6 +654,41 @@ alter table idxpart2 drop column c;
b | integer | | |
drop table idxpart, idxpart2;
+--
+-- Drop column also drop all indexes that have whole-row references.
+--
+-- Change column data type should error out because the whole-row referenced
+-- indexex is still needed.
+create table idxpart (a int, b int, c int) partition by range (a);
+create table idxpart1 partition of idxpart for values from (2000) to (3000);
+create index idxpart_idx1 on idxpart1((idxpart1 is not null));
+alter table idxpart alter column b set data type int8; --error
+ERROR: cannot alter table "idxpart1" because index idxpart_idx1 uses its row type
+HINT: You might need to drop index idxpart_idx1 first
+drop index idxpart_idx1;
+create index idxpart_idx2 on idxpart1(a) where idxpart1 is not null;
+alter table idxpart alter column c set data type int8; --error
+ERROR: cannot alter table "idxpart1" because index idxpart_idx2 uses its row type
+HINT: You might need to drop index idxpart_idx2 first
+alter table idxpart drop column c;
+\d idxpart
+ Partitioned table "public.idxpart"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+Partition key: RANGE (a)
+Number of partitions: 1 (Use \d+ to list them.)
+
+\d idxpart1
+ Table "public.idxpart1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+Partition of: idxpart FOR VALUES FROM (2000) TO (3000)
+
+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 b7f6efdd814..cb2f3fc7d5e 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -165,6 +165,22 @@ 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 all check constraints that have whole-row references.
+--
+-- Change column data type should error out because the whole-row referenced
+-- check constraint is still needed.
+-- no need to worry about partitioned tables, since the whole-row check constraint
+-- can not span multi relation.
+CREATE TABLE wholerow_check_tbl (
+ city int,
+ CONSTRAINT cc0 CHECK (wholerow_check_tbl is null) NOT ENFORCED,
+ CONSTRAINT cc1 CHECK (wholerow_check_tbl is not null) NOT ENFORCED);
+ALTER TABLE wholerow_check_tbl ALTER COLUMN city SET DATA TYPE INT8;
+ALTER TABLE wholerow_check_tbl DROP COLUMN city; -- ok
+\d wholerow_check_tbl
+DROP TABLE wholerow_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..0dfef988d0a 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -295,6 +295,26 @@ alter table idxpart2 drop column c;
\d idxpart2
drop table idxpart, idxpart2;
+--
+-- Drop column also drop all indexes that have whole-row references.
+--
+-- Change column data type should error out because the whole-row referenced
+-- indexex is still needed.
+create table idxpart (a int, b int, c int) partition by range (a);
+create table idxpart1 partition of idxpart for values from (2000) to (3000);
+
+create index idxpart_idx1 on idxpart1((idxpart1 is not null));
+alter table idxpart alter column b set data type int8; --error
+drop index idxpart_idx1;
+
+create index idxpart_idx2 on idxpart1(a) where idxpart1 is not null;
+alter table idxpart alter column c set data type int8; --error
+alter table idxpart drop column c;
+
+\d idxpart
+\d idxpart1
+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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: let ALTER TABLE DROP COLUMN drop whole-row referenced object
@ 2026-04-30 04:57 lakshmi <[email protected]>
parent: jian he <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: lakshmi @ 2026-04-30 04:57 UTC (permalink / raw)
To: pgsql-hackers; +Cc: 金 <[email protected]>; Kirill Reshke <[email protected]>; Chao Li <[email protected]>; jian he <[email protected]>
Hi,
I tested the latest v8-0001 patch on current master and wanted to share my
observations.
Before applying the patch, when dropping a column, constraints and indexes
that referenced the whole row (for example, CHECK (ts = ROW(...)) or
expressions using ts) were not removed. This left the table in an
inconsistent state and resulted in errors during inserts.
After applying the patch, these objects are correctly detected and removed
when the column is dropped. The table remains clean, and inserts work as
expected.
I also tried a few additional scenarios:
-
DROP COLUMN with CASCADE—behaved as expected, no leftover objects
-
normal column-level constraints — still handled correctly (no regression)
-
multiple whole-row constraints — all removed properly
-
ALTER COLUMN TYPE — correctly throws an error when a whole-row
constraint exists
Overall, the behavior looks correct and consistent based on these tests.
Thanks for working on this!
lakshmi
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: let ALTER TABLE DROP COLUMN drop whole-row referenced object
@ 2026-05-25 06:43 jian he <[email protected]>
parent: lakshmi <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: jian he @ 2026-05-25 06:43 UTC (permalink / raw)
To: lakshmi <[email protected]>; +Cc: pgsql-hackers; 金 <[email protected]>; Kirill Reshke <[email protected]>; Chao Li <[email protected]>
Hi.
[1] rebuilds whole-row dependencies for ALTER COLUMN SET EXPRESSION,
this thread addresses whole-row dependencies for ALTER TABLE DROP
COLUMN and ALTER COLUMN SET DATA TYPE.
Overall, the attached v9 doesn't include any major changes.
It just contains some refactoring to make the coding style consistent with [1].
[1] https://commitfest.postgresql.org/patch/6755
--
jian
https://www.enterprisedb.com/
Attachments:
[text/x-patch] v9-0002-fix-DDL-wholerow-referenced-triggers.patch (8.2K, 2-v9-0002-fix-DDL-wholerow-referenced-triggers.patch)
download | inline diff:
From 10760e35806f8ac5608e246eb8d0b4baa5779f4f Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 25 May 2026 14:19:45 +0800
Subject: [PATCH v9 2/3] fix DDL wholerow referenced triggers
ALTER TABLE DROP COLUMN should fail if any trigger's WHEN clause contains a
whole-row reference. To do this, we record a dependency between the
relation and the dependent trigger in RememberWholeRowDependentForRebuilding.
Later, performMultipleDeletions handles the deletion logic.
ALTER COLUMN SET DATA TYPE fundamentally changes the table's row type. Rows
with different column data types cannot be compared (see record_eq). Therefore,
we must error out otherwise, any trigger WHEN clause contains whole-row
references may constantly error.
discussion: https://postgr.es/m/CACJufxGA6KVQy7DbHGLVw9s9KKmpGyZt5ME6C7kEfjDpr2wZCw@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/6055
---
src/backend/commands/tablecmds.c | 38 ++++++++++++++++++++++
src/test/regress/expected/foreign_data.out | 14 ++++++++
src/test/regress/expected/triggers.out | 28 ++++++++++++++++
src/test/regress/sql/foreign_data.sql | 10 ++++++
src/test/regress/sql/triggers.sql | 17 ++++++++++
5 files changed, 107 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 4aa582511bc..bd8e29de1b1 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -24156,4 +24156,42 @@ RememberWholeRowDependentForRebuilding(AlteredTableInfo *tab, AlterTableType sub
}
systable_endscan(indscan);
table_close(pg_index, AccessShareLock);
+
+ /* Now checking trigger whole-row references */
+ if (rel->trigdesc != NULL)
+ {
+ for (int i = 0; i < rel->trigdesc->numtriggers; i++)
+ {
+ Bitmapset *expr_attrs = NULL;
+ Trigger *trig = &rel->trigdesc->triggers[i];
+
+ if (trig->tgqual == NULL)
+ continue;
+
+ expr = stringToNode(trig->tgqual);
+
+ pull_varattnos(expr, PRS2_OLD_VARNO, &expr_attrs);
+
+ pull_varattnos(expr, PRS2_NEW_VARNO, &expr_attrs);
+
+ /*
+ * If the triger WHEN qual contains whole-row reference then
+ * remember it
+ */
+ if (bms_is_member(InvalidAttrNumber - FirstLowInvalidHeapAttributeNumber,
+ expr_attrs))
+ {
+ ObjectAddress trig_obj;
+
+ ObjectAddressSet(trig_obj, TriggerRelationId, trig->tgoid);
+
+ /*
+ * The dependency between the trigger and its relation is
+ * DEPENDENCY_NORMAL
+ */
+ RememberWholeRowDependent(tab, subtype, rel, attnum,
+ &trig_obj, DEPENDENCY_NORMAL);
+ }
+ }
+ }
}
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index d8e4cb12c3d..4e2acad9dd9 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -1427,6 +1427,20 @@ DROP TRIGGER trigtest_before_stmt ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_after_stmt ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1;
+CREATE TRIGGER trigtest_before_stmt BEFORE INSERT OR UPDATE
+ON foreign_schema.foreign_table_1
+FOR EACH ROW
+WHEN (new IS NOT NULL)
+EXECUTE PROCEDURE dummy_trigger();
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 ALTER COLUMN c7 SET DATA TYPE bigint; -- error
+ERROR: cannot alter table "foreign_table_1" because trigger trigtest_before_stmt on foreign table foreign_schema.foreign_table_1 uses its row type
+HINT: You might need to drop trigger trigtest_before_stmt on foreign table foreign_schema.foreign_table_1 first
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 DROP COLUMN c7; -- error
+ERROR: cannot drop column c7 of foreign table foreign_schema.foreign_table_1 because other objects depend on it
+DETAIL: trigger trigtest_before_stmt on foreign table foreign_schema.foreign_table_1 depends on column c7 of foreign table foreign_schema.foreign_table_1
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 DROP COLUMN c7 CASCADE; -- ok
+NOTICE: drop cascades to trigger trigtest_before_stmt on foreign table foreign_schema.foreign_table_1
DROP FUNCTION dummy_trigger();
-- Table inheritance
CREATE TABLE fd_pt1 (
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 8fcb33ac81a..e10a43e5f3f 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -227,6 +227,34 @@ ERROR: trigger "no_such_trigger" for table "main_table" does not exist
COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS 'right';
COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS NULL;
--
+-- test triggers with WHEN clause contain wholerow reference
+--
+CREATE TABLE test_tbl1 (a int, b int) PARTITION BY RANGE (a);
+CREATE TABLE test_tbl1p1 PARTITION OF test_tbl1 FOR VALUES FROM (0) TO (1000);
+CREATE TRIGGER test_tbl1p1_trig
+ BEFORE INSERT OR UPDATE ON test_tbl1p1 FOR EACH ROW
+ WHEN (new = ROW (1, 1))
+ EXECUTE PROCEDURE trigger_func ('test_tbl1p1');
+ALTER TABLE test_tbl1 ALTER COLUMN b SET DATA TYPE bigint; -- error
+ERROR: cannot alter table "test_tbl1p1" because trigger test_tbl1p1_trig on table test_tbl1p1 uses its row type
+HINT: You might need to drop trigger test_tbl1p1_trig on table test_tbl1p1 first
+ALTER TABLE test_tbl1 DROP COLUMN b; -- error
+ERROR: cannot drop desired object(s) because other objects depend on them
+DETAIL: trigger test_tbl1p1_trig on table test_tbl1p1 depends on column b of table test_tbl1p1
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+ALTER TABLE test_tbl1 DROP COLUMN b CASCADE; -- ok
+NOTICE: drop cascades to trigger test_tbl1p1_trig on table test_tbl1p1
+\d+ test_tbl1
+ Partitioned table "public.test_tbl1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+Partition key: RANGE (a)
+Partitions:
+ test_tbl1p1 FOR VALUES FROM (0) TO (1000)
+
+DROP TABLE test_tbl1;
+--
-- test triggers with WHEN clause
--
CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table
diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql
index ed78052b1e2..5f2eb0e3279 100644
--- a/src/test/regress/sql/foreign_data.sql
+++ b/src/test/regress/sql/foreign_data.sql
@@ -649,6 +649,16 @@ DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_after_stmt ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1;
+CREATE TRIGGER trigtest_before_stmt BEFORE INSERT OR UPDATE
+ON foreign_schema.foreign_table_1
+FOR EACH ROW
+WHEN (new IS NOT NULL)
+EXECUTE PROCEDURE dummy_trigger();
+
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 ALTER COLUMN c7 SET DATA TYPE bigint; -- error
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 DROP COLUMN c7; -- error
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 DROP COLUMN c7 CASCADE; -- ok
+
DROP FUNCTION dummy_trigger();
-- Table inheritance
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index 2285e90110e..1823c2cfb8d 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -158,6 +158,23 @@ COMMENT ON TRIGGER no_such_trigger ON main_table IS 'wrong';
COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS 'right';
COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS NULL;
+--
+-- test triggers with WHEN clause contain wholerow reference
+--
+CREATE TABLE test_tbl1 (a int, b int) PARTITION BY RANGE (a);
+CREATE TABLE test_tbl1p1 PARTITION OF test_tbl1 FOR VALUES FROM (0) TO (1000);
+
+CREATE TRIGGER test_tbl1p1_trig
+ BEFORE INSERT OR UPDATE ON test_tbl1p1 FOR EACH ROW
+ WHEN (new = ROW (1, 1))
+ EXECUTE PROCEDURE trigger_func ('test_tbl1p1');
+
+ALTER TABLE test_tbl1 ALTER COLUMN b SET DATA TYPE bigint; -- error
+ALTER TABLE test_tbl1 DROP COLUMN b; -- error
+ALTER TABLE test_tbl1 DROP COLUMN b CASCADE; -- ok
+\d+ test_tbl1
+DROP TABLE test_tbl1;
+
--
-- test triggers with WHEN clause
--
--
2.34.1
[text/x-patch] v9-0003-fix-DDL-wholerow-referenced-policies.patch (13.5K, 3-v9-0003-fix-DDL-wholerow-referenced-policies.patch)
download | inline diff:
From 575265b67813e860a382971d5e62fae9c745ba72 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Sat, 23 May 2026 21:40:23 +0800
Subject: [PATCH v9 3/3] fix DDL wholerow referenced policies
ALTER TABLE DROP COLUMN must fail if any policy's WHEN clause contains a
whole-row reference. To achieve this, we record a dependency between the
relation and the dependent policy in RememberWholeRowDependentForRebuilding.
Later, performMultipleDeletions handles the deletion logic.
ALTER COLUMN SET DATA TYPE fundamentally changes the table's row type. Rows
with different column data types cannot be compared (see record_eq). Therefore,
we must error out otherwise, any policy WHEN clause contains whole-row
references may constantly error.
Policy have a DEPENDENCY_NORMAL type with their source table. Policy's qual and
with check qual are quite unconstrained (allowing subqueries), we can't reliably
use pull_varattnos to detect if they contain subqueries. A further complication
is that the qual and with check qual whole-row Var may not only references their
own table but also for other unrelated tables.
Therefore We should check pg_depend, not pg_policy, to see if dropping this
table affects any policy objects. After collecting the policies impacted by the
ALTER TABLE command, check each policy qual and with check qual, see if
whole-row references or not.
discussion: https://postgr.es/m/CACJufxGA6KVQy7DbHGLVw9s9KKmpGyZt5ME6C7kEfjDpr2wZCw@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/6055
---
src/backend/commands/tablecmds.c | 130 ++++++++++++++++++++++
src/backend/optimizer/util/var.c | 56 ++++++++++
src/include/optimizer/optimizer.h | 1 +
src/test/regress/expected/rowsecurity.out | 29 ++++-
src/test/regress/sql/rowsecurity.sql | 17 +++
src/tools/pgindent/typedefs.list | 1 +
6 files changed, 232 insertions(+), 2 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index bd8e29de1b1..ed30c372239 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -790,6 +790,8 @@ static void RememberWholeRowDependentForRebuilding(AlteredTableInfo *tab, AlterT
Relation rel, AttrNumber attnum,
const char *colName);
+static List *GetAllRelAssociatedPolicies(Relation rel);
+
/* ----------------------------------------------------------------
* DefineRelation
* Creates a new relation.
@@ -23976,6 +23978,7 @@ RememberWholeRowDependentForRebuilding(AlteredTableInfo *tab, AlterTableType sub
ScanKeyData skey;
Relation pg_index;
Relation pg_constraint;
+ Relation pg_policy;
SysScanDesc indscan;
SysScanDesc conscan;
HeapTuple constrTuple;
@@ -23984,6 +23987,8 @@ RememberWholeRowDependentForRebuilding(AlteredTableInfo *tab, AlterTableType sub
char *exprString = NULL;
bool isnull;
List *wholerow_idxoids = NIL;
+ List *pols = NIL;
+ Oid reltypid;
Assert(subtype == AT_AlterColumnType || subtype == AT_DropColumn);
@@ -24194,4 +24199,129 @@ RememberWholeRowDependentForRebuilding(AlteredTableInfo *tab, AlterTableType sub
}
}
}
+
+ /* Now checking policy whole-row references */
+ reltypid = get_rel_type_id(RelationGetRelid(rel));
+
+ pg_policy = table_open(PolicyRelationId, AccessShareLock);
+
+ pols = GetAllRelAssociatedPolicies(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);
+
+ /*
+ * The dependency between the policy and it's relation is
+ * DEPENDENCY_NORMAL
+ */
+ RememberWholeRowDependent(tab, subtype, rel, attnum,
+ &pol_obj, DEPENDENCY_NORMAL);
+
+ continue;
+ }
+ }
+
+ 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);
+
+ /*
+ * The dependency between the policy and it's relation is
+ * DEPENDENCY_NORMAL
+ */
+ RememberWholeRowDependent(tab, subtype, rel, attnum,
+ &pol_obj, DEPENDENCY_NORMAL);
+ }
+ }
+ }
+ systable_endscan(sscan);
+ }
+ table_close(pg_policy, AccessShareLock);
+}
+
+/*
+ * GetAllRelAssociatedPolicies
+ *
+ * Returns a list of OIDs of all row-level security policies associated with the
+ * given relation.
+ */
+static List *
+GetAllRelAssociatedPolicies(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..75f19271974 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,56 @@ 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 existsence 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/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 3a5e82c35bd..6b6fe994033 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -2731,6 +2731,32 @@ 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.
+DROP TABLE part_document;
+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 uaccount ALTER COLUMN seclv SET DATA TYPE BIGINT; -- error
+ERROR: cannot alter table "uaccount" because policy p7 on table document uses its row type
+HINT: You might need to drop policy p7 on table document first
+ALTER TABLE document ALTER COLUMN dummy SET DATA TYPE BIGINT; -- error
+ERROR: cannot alter table "document" because policy p7 on table document uses its row type
+HINT: You might need to drop policy p7 on table document 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 uaccount DROP COLUMN seclv; -- error
+ERROR: cannot drop column seclv of table uaccount because other objects depend on it
+DETAIL: policy p7 on table document depends on column seclv of table uaccount
+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
+ALTER TABLE uaccount DROP COLUMN seclv CASCADE; -- ok
--
-- ROLE/GROUP
--
@@ -5199,12 +5225,11 @@ drop table rls_t, test_t;
--
RESET SESSION AUTHORIZATION;
DROP SCHEMA regress_rls_schema CASCADE;
-NOTICE: drop cascades to 30 other objects
+NOTICE: drop cascades to 29 other objects
DETAIL: drop cascades to function f_leak(text)
drop cascades to table uaccount
drop cascades to table category
drop cascades to table document
-drop cascades to table part_document
drop cascades to table dependent
drop cascades to table rec1
drop cascades to table rec2
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 6b3566271df..32f2dda73a4 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -1219,6 +1219,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.
+DROP TABLE part_document;
+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 uaccount ALTER COLUMN seclv SET DATA TYPE BIGINT; -- error
+ALTER TABLE document ALTER COLUMN dummy SET DATA TYPE BIGINT; -- error
+
+ALTER TABLE document DROP COLUMN dummy; -- error
+ALTER TABLE uaccount DROP COLUMN seclv; -- error
+ALTER TABLE document DROP COLUMN dummy CASCADE; -- ok
+ALTER TABLE uaccount DROP COLUMN seclv CASCADE; -- ok
+
--
-- ROLE/GROUP
--
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 8cf40c87043..9236525b44d 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3692,6 +3692,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
[text/x-patch] v9-0001-fix-DDL-wholerow-referenced-constraints-and-indexes.patch (16.1K, 4-v9-0001-fix-DDL-wholerow-referenced-constraints-and-indexes.patch)
download | inline diff:
From edd9bb976d37dbd00c66d8ffeb6b91da49cf83ec Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 25 May 2026 14:22:45 +0800
Subject: [PATCH v9 1/3] fix DDL wholerow referenced constraints and indexes
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
ALTER TABLE DROP COLUMN should remove indexes or constraints contain whole-row
references.
ALTER COLUMN SET DATA TYPE fundamentally changes the table’s record type; At
present, we cannot compare records that contain columns of dissimilar types, see
function record_eq. As a result, ALTER COLUMN SET DATA TYPE does not work for
whole-row reference objects (such as constraints and indexes), and must
therefore raise an error.
discussion: https://postgr.es/m/CACJufxGA6KVQy7DbHGLVw9s9KKmpGyZt5ME6C7kEfjDpr2wZCw@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/6055
---
src/backend/commands/tablecmds.c | 255 ++++++++++++++++++++++
src/test/regress/expected/constraints.out | 23 ++
src/test/regress/expected/indexing.out | 32 +++
src/test/regress/sql/constraints.sql | 17 ++
src/test/regress/sql/indexing.sql | 17 ++
5 files changed, 344 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 1e0bacf85fc..4aa582511bc 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -786,6 +786,9 @@ 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);
/* ----------------------------------------------------------------
* DefineRelation
@@ -9395,6 +9398,10 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
List *children;
ObjectAddress object;
bool is_expr;
+ AlteredTableInfo *tab;
+
+ /* Find or create work queue entry for this table */
+ tab = ATGetQueueEntry(wqueue, rel);
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
@@ -9467,6 +9474,15 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
ReleaseSysCache(tuple);
+ /*
+ * Record dependencies between this relation and any objects containing
+ * whole-row Var references. performMultipleDeletions will take care of
+ * removing them dependencies later.
+ */
+ RememberWholeRowDependentForRebuilding(tab, AT_DropColumn, rel, attnum, colName);
+
+ 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
@@ -15118,6 +15134,15 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
*/
RememberAllDependentForRebuilding(tab, AT_AlterColumnType, rel, attnum, colName);
+ /*
+ * Record dependencies between this relation and any objects containing
+ * whole-row Var references. We either error out saying such a dependency
+ * is not allowed, or we install the dependencies.
+ */
+ RememberWholeRowDependentForRebuilding(tab, AT_AlterColumnType, rel, attnum, colName);
+
+ CommandCounterIncrement();
+
/*
* Now scan for dependencies of this column on other things. The only
* things we should find are the dependency on the column datatype and
@@ -23902,3 +23927,233 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* Restore the userid and security context. */
SetUserIdAndSecContext(save_userid, save_sec_context);
}
+
+/*
+ * Record dependencies between objects contains whole-row Var references
+ * (indexes, CHECK constraints, etc.) and the relation, or report an
+ * error.
+*/
+static void
+RememberWholeRowDependent(AlteredTableInfo *tab, AlterTableType subtype,
+ Relation rel, AttrNumber attnum,
+ const ObjectAddress *depender,
+ DependencyType behavior)
+{
+ if (subtype == AT_AlterColumnType)
+ ereport(ERROR,
+ errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("cannot alter table \"%s\" because %s uses its row type",
+ RelationGetRelationName(rel),
+ getObjectDescription(depender, false)),
+ errhint("You might need to drop %s first",
+ getObjectDescription(depender, false)));
+ else
+ {
+ ObjectAddress referenced;
+
+ ObjectAddressSubSet(referenced, RelationRelationId,
+ RelationGetRelid(rel), attnum);
+
+ recordDependencyOn(depender, &referenced, behavior);
+ }
+}
+
+/*
+ * Record dependencies between objects contains whole-row Var references
+ * (indexes, CHECK constraints, etc.) and the relation, or report an
+ * error.
+ *
+ * See also RememberAllDependentForRebuilding, which handles non-whole-row Var
+ * references.
+ *
+ * Currently used by ALTER COLUMN SET DATA TYPE and ALTER TABLE DROP COLUMN.
+ */
+static void
+RememberWholeRowDependentForRebuilding(AlteredTableInfo *tab, AlterTableType subtype,
+ Relation rel, AttrNumber attnum, const char *colName)
+{
+ Node *expr = NULL;
+ ScanKeyData skey;
+ Relation pg_index;
+ Relation pg_constraint;
+ SysScanDesc indscan;
+ SysScanDesc conscan;
+ HeapTuple constrTuple;
+ HeapTuple indexTuple;
+ Datum exprDatum;
+ char *exprString = NULL;
+ bool isnull;
+ List *wholerow_idxoids = NIL;
+
+ Assert(subtype == AT_AlterColumnType || subtype == AT_DropColumn);
+
+ /*
+ * Checking CHECK constraint with whole-row references, now.
+ */
+ 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))
+ {
+ ObjectAddress con_obj;
+
+ ObjectAddressSet(con_obj, ConstraintRelationId, conform->oid);
+
+ /*
+ * The dependency between the CHECK constraint and its
+ * relation is DEPENDENCY_AUTO
+ */
+ RememberWholeRowDependent(tab, subtype, rel, attnum,
+ &con_obj, DEPENDENCY_AUTO);
+ }
+ }
+ }
+ 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
+ */
+ 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))
+ {
+ 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))
+ {
+ ObjectAddress idx_obj;
+
+ ObjectAddressSet(idx_obj, RelationRelationId,
+ index->indexrelid);
+
+ wholerow_idxoids = lappend_oid(wholerow_idxoids,
+ index->indexrelid);
+
+ /*
+ * The index has a DEPENDENCY_AUTO relationship with its
+ * relation
+ */
+ RememberWholeRowDependent(tab, subtype, rel, attnum,
+ &idx_obj, DEPENDENCY_AUTO);
+
+ 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))
+ {
+ ObjectAddress idx_obj;
+
+ ObjectAddressSet(idx_obj, RelationRelationId, index->indexrelid);
+
+ wholerow_idxoids = lappend_oid(wholerow_idxoids,
+ index->indexrelid);
+
+ /*
+ * The index has a DEPENDENCY_AUTO relationship with its
+ * relation
+ */
+ RememberWholeRowDependent(tab, subtype, rel, attnum,
+ &idx_obj, DEPENDENCY_AUTO);
+ }
+ }
+ }
+ systable_endscan(indscan);
+ table_close(pg_index, AccessShareLock);
+}
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index e54fec7fb57..658dd421dba 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -254,6 +254,29 @@ ERROR: system column "ctid" reference in check constraint is invalid
LINE 3: CHECK (NOT (is_capital AND ctid::text = 'sys_col_check...
^
--
+-- Drop column should also drop all check constraints that contains whole-row references
+--
+-- Change column data type should fail since whole-row referenced check constraint still exists
+--
+-- No need to worry about partitioned tables, since the whole-row check constraint
+-- can not span multi relations
+CREATE TABLE wholerow_check_tbl (
+ city int,
+ state int,
+ CONSTRAINT cc0 CHECK (wholerow_check_tbl is null) NOT ENFORCED,
+ CONSTRAINT cc1 CHECK (wholerow_check_tbl is not null) NOT ENFORCED);
+ALTER TABLE wholerow_check_tbl ALTER COLUMN city SET DATA TYPE INT8; -- error
+ERROR: cannot alter table "wholerow_check_tbl" because constraint cc0 on table wholerow_check_tbl uses its row type
+HINT: You might need to drop constraint cc0 on table wholerow_check_tbl first
+ALTER TABLE wholerow_check_tbl DROP COLUMN city; -- ok
+\d wholerow_check_tbl
+ Table "public.wholerow_check_tbl"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ state | integer | | |
+
+DROP TABLE wholerow_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 929feda6fa3..424f7ea3f68 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -759,6 +759,38 @@ alter table idxpart2 drop column c;
b | integer | | |
drop table idxpart, idxpart2;
+-- Drop column should also drop all indexes that contains whole-row references
+-- Change column data type should fail if whole-row referenced indexes exists
+create table idxpart (a int, b int, c int) partition by range (a);
+create table idxpart1 partition of idxpart for values from (2000) to (3000);
+create index idxpart_idx1 on idxpart1((idxpart1 is not null));
+alter table idxpart alter column b set data type bigint; -- error
+ERROR: cannot alter table "idxpart1" because index idxpart_idx1 uses its row type
+HINT: You might need to drop index idxpart_idx1 first
+drop index idxpart_idx1;
+create index idxpart_idx2 on idxpart1(a) where idxpart1 is not null;
+alter table idxpart alter column c set data type bigint; -- error
+ERROR: cannot alter table "idxpart1" because index idxpart_idx2 uses its row type
+HINT: You might need to drop index idxpart_idx2 first
+alter table idxpart drop column c; -- ok
+\d idxpart
+ Partitioned table "public.idxpart"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+Partition key: RANGE (a)
+Number of partitions: 1 (Use \d+ to list them.)
+
+\d idxpart1
+ Table "public.idxpart1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+Partition of: idxpart FOR VALUES FROM (2000) TO (3000)
+
+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 dc133b124bb..2600c9f518d 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -165,6 +165,23 @@ 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 should also drop all check constraints that contains whole-row references
+--
+-- Change column data type should fail since whole-row referenced check constraint still exists
+--
+-- No need to worry about partitioned tables, since the whole-row check constraint
+-- can not span multi relations
+CREATE TABLE wholerow_check_tbl (
+ city int,
+ state int,
+ CONSTRAINT cc0 CHECK (wholerow_check_tbl is null) NOT ENFORCED,
+ CONSTRAINT cc1 CHECK (wholerow_check_tbl is not null) NOT ENFORCED);
+ALTER TABLE wholerow_check_tbl ALTER COLUMN city SET DATA TYPE INT8; -- error
+ALTER TABLE wholerow_check_tbl DROP COLUMN city; -- ok
+\d wholerow_check_tbl
+DROP TABLE wholerow_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 3d43af3323c..9bc5b83a5e6 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -354,6 +354,23 @@ alter table idxpart2 drop column c;
\d idxpart2
drop table idxpart, idxpart2;
+-- Drop column should also drop all indexes that contains whole-row references
+-- Change column data type should fail if whole-row referenced indexes exists
+create table idxpart (a int, b int, c int) partition by range (a);
+create table idxpart1 partition of idxpart for values from (2000) to (3000);
+
+create index idxpart_idx1 on idxpart1((idxpart1 is not null));
+alter table idxpart alter column b set data type bigint; -- error
+drop index idxpart_idx1;
+
+create index idxpart_idx2 on idxpart1(a) where idxpart1 is not null;
+alter table idxpart alter column c set data type bigint; -- error
+alter table idxpart drop column c; -- ok
+
+\d idxpart
+\d idxpart1
+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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-05-25 06:43 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-19 09:59 Re: let ALTER TABLE DROP COLUMN drop whole-row referenced object =?GBK?B?vfAg?= <[email protected]>
2026-02-28 07:27 ` jian he <[email protected]>
2026-04-30 04:57 ` lakshmi <[email protected]>
2026-05-25 06:43 ` jian he <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox