From 0304acea80d0f336c47c8d1f38d71602bf08f4da Mon Sep 17 00:00:00 2001 From: jian he 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