v7-0002-disallow-drop-or-change-column-if-wholerow-trigger-exists.patch
application/x-patch
Filename: v7-0002-disallow-drop-or-change-column-if-wholerow-trigger-exists.patch
Type: application/x-patch
Part: 2
From 66061613623e4b1faa15d048d37fdfc3d9136904 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Sat, 27 Dec 2025 10:55:24 +0800
Subject: [PATCH v7 2/3] disallow drop or change column if wholerow trigger
exists
1. ALTER TABLE DROP COLUMN
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 error out as expected.
2. ALTER COLUMN SET DATA TYPE
ALTER COLUMN SET DATA TYPE must error out if any trigger have whole-row reference
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
---
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 714ab75e7b3..1e28f3af50b 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -23558,4 +23558,39 @@ recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object,
}
ReleaseSysCache(indexTuple);
}
+
+ if (rel->trigdesc != NULL)
+ {
+ ObjectAddress trig_obj;
+
+ TriggerDesc *trigdesc = rel->trigdesc;
+
+ for (int i = 0; i < trigdesc->numtriggers; i++)
+ {
+ Trigger *trig = &trigdesc->triggers[i];
+
+ if (trig->tgqual != NULL)
+ {
+ expr_attrs = NULL;
+
+ expr = stringToNode(trig->tgqual);
+
+ pull_varattnos(expr, PRS2_OLD_VARNO, &expr_attrs);
+
+ pull_varattnos(expr, PRS2_NEW_VARNO, &expr_attrs);
+
+ find_wholerow = bms_is_member(0 - FirstLowInvalidHeapAttributeNumber,
+ expr_attrs);
+ if (find_wholerow)
+ {
+ 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..c5b0ce10a60 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 1eb8fba0953..45389e8c94f 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..d36ec955861 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 5f7f75d7ba5..3236efec44f 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