From 80ba4bb383538a2ee846fece6a7b8da9518b6866 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Thu, 22 Jul 2021 18:33:47 -0400 Subject: Make ALTER TRIGGER RENAME consistent for partitioned tables Renaming triggers on partitioned tables had two problems: first, it did not recurse to renaming the triggers on the partitions; and second, it failed to prohibit renaming clone triggers. Having triggers with different names in partitions is pointless, and furthermore pg_dump would not preserve names for partitions anyway. Not backpatched -- making the ALTER TRIGGER throw an error in stable versions might cause problems for existing scripts. Co-authored-by: Arne Roland Co-authored-by: Álvaro Herrera Reviewed-by: Zhihong Yu Discussion: https://postgr.es/m/d0fd7040c2fb4de1a111b9d9ccc456b8@index.de --- src/test/regress/expected/triggers.out | 76 ++++++++++++++++++++++++++++++++++ src/test/regress/sql/triggers.sql | 47 +++++++++++++++++++++ 2 files changed, 123 insertions(+) (limited to 'src/test') diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index 5254447cf8e..564eb4faa24 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -3410,3 +3410,79 @@ for each statement execute function trigger_function1(); delete from convslot_test_parent; NOTICE: trigger = bdt_trigger, old_table = (111,tutu), (311,tutu) drop table convslot_test_child, convslot_test_parent; +-- Test trigger renaming on partitioned tables +create table grandparent (id int, primary key (id)) partition by range (id); +create table middle partition of grandparent for values from (1) to (10) +partition by range (id); +create table chi partition of middle for values from (1) to (5); +create table cho partition of middle for values from (6) to (10); +create function f () returns trigger as +$$ begin return new; end; $$ +language plpgsql; +create trigger a after insert on grandparent +for each row execute procedure f(); +alter trigger a on grandparent rename to b; +select tgrelid::regclass, tgname, +(select tgname from pg_trigger tr where tr.oid = pg_trigger.tgparentid) parent_tgname +from pg_trigger where tgrelid in (select relid from pg_partition_tree('grandparent')) +order by tgname, tgrelid::regclass::text; + tgrelid | tgname | parent_tgname +-------------+--------+--------------- + chi | b | b + cho | b | b + grandparent | b | + middle | b | b +(4 rows) + +alter trigger a on only grandparent rename to b; -- ONLY not supported +ERROR: syntax error at or near "only" +LINE 1: alter trigger a on only grandparent rename to b; + ^ +alter trigger b on middle rename to c; -- can't rename trigger on partition +ERROR: cannot rename trigger "b" on table "middle" +HINT: Rename trigger on partitioned table "grandparent" instead. +create trigger c after insert on middle +for each row execute procedure f(); +alter trigger b on grandparent rename to c; +ERROR: trigger "c" for relation "middle" already exists +-- Rename cascading does not affect statement triggers +create trigger p after insert on grandparent for each statement execute function f(); +create trigger p after insert on middle for each statement execute function f(); +alter trigger p on grandparent rename to q; +select tgrelid::regclass, tgname, +(select tgname from pg_trigger tr where tr.oid = pg_trigger.tgparentid) parent_tgname +from pg_trigger where tgrelid in (select relid from pg_partition_tree('grandparent')) +order by tgname, tgrelid::regclass::text; + tgrelid | tgname | parent_tgname +-------------+--------+--------------- + chi | b | b + cho | b | b + grandparent | b | + middle | b | b + chi | c | c + cho | c | c + middle | c | + middle | p | + grandparent | q | +(9 rows) + +drop table grandparent; +-- Trigger renaming does not recurse on legacy inheritance +create table parent (a int); +create table child () inherits (parent); +create trigger parenttrig after insert on parent +for each row execute procedure f(); +create trigger parenttrig after insert on child +for each row execute procedure f(); +alter trigger parenttrig on parent rename to anothertrig; +\d+ child + Table "public.child" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+--------------+------------- + a | integer | | | | plain | | +Triggers: + parenttrig AFTER INSERT ON child FOR EACH ROW EXECUTE FUNCTION f() +Inherits: parent + +drop table parent, child; +drop function f(); diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index 7b73ee20a1b..fb94eca3ed2 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -2572,3 +2572,50 @@ for each statement execute function trigger_function1(); delete from convslot_test_parent; drop table convslot_test_child, convslot_test_parent; + +-- Test trigger renaming on partitioned tables +create table grandparent (id int, primary key (id)) partition by range (id); +create table middle partition of grandparent for values from (1) to (10) +partition by range (id); +create table chi partition of middle for values from (1) to (5); +create table cho partition of middle for values from (6) to (10); +create function f () returns trigger as +$$ begin return new; end; $$ +language plpgsql; +create trigger a after insert on grandparent +for each row execute procedure f(); + +alter trigger a on grandparent rename to b; +select tgrelid::regclass, tgname, +(select tgname from pg_trigger tr where tr.oid = pg_trigger.tgparentid) parent_tgname +from pg_trigger where tgrelid in (select relid from pg_partition_tree('grandparent')) +order by tgname, tgrelid::regclass::text; +alter trigger a on only grandparent rename to b; -- ONLY not supported +alter trigger b on middle rename to c; -- can't rename trigger on partition +create trigger c after insert on middle +for each row execute procedure f(); +alter trigger b on grandparent rename to c; + +-- Rename cascading does not affect statement triggers +create trigger p after insert on grandparent for each statement execute function f(); +create trigger p after insert on middle for each statement execute function f(); +alter trigger p on grandparent rename to q; +select tgrelid::regclass, tgname, +(select tgname from pg_trigger tr where tr.oid = pg_trigger.tgparentid) parent_tgname +from pg_trigger where tgrelid in (select relid from pg_partition_tree('grandparent')) +order by tgname, tgrelid::regclass::text; + +drop table grandparent; + +-- Trigger renaming does not recurse on legacy inheritance +create table parent (a int); +create table child () inherits (parent); +create trigger parenttrig after insert on parent +for each row execute procedure f(); +create trigger parenttrig after insert on child +for each row execute procedure f(); +alter trigger parenttrig on parent rename to anothertrig; +\d+ child + +drop table parent, child; +drop function f(); -- cgit v1.2.3