Skip to content

Commit fed35bd

Browse files
alvherrealvherre
committed
Preserve firing-on state when cloning row triggers to partitions
When triggers are cloned from partitioned tables to their partitions, the 'tgenabled' flag (origin/replica/always/disable) was not propagated. Make it so that the flag on the trigger on partition is initially set to the same value as on the partitioned table. Add a test case to verify the behavior. Backpatch to 11, where this appeared in commit 86f5759. Author: Álvaro Herrera <alvherre@alvh.no-ip.org> Reported-by: Justin Pryzby <pryzby@telsasoft.com> Discussion: https://postgr.es/m/20200930223450.GA14848@telsasoft.com
1 parent 85a8c3a commit fed35bd

File tree

5 files changed

+121
-10
lines changed

5 files changed

+121
-10
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -15675,10 +15675,10 @@ CloneRowTriggersToPartition(Relation parent, Relation partition)
1567515675
trigStmt->initdeferred = trigForm->tginitdeferred;
1567615676
trigStmt->constrrel = NULL; /* passed separately */
1567715677

15678-
CreateTrigger(trigStmt, NULL, RelationGetRelid(partition),
15679-
trigForm->tgconstrrelid, InvalidOid, InvalidOid,
15680-
trigForm->tgfoid, HeapTupleGetOid(tuple), qual,
15681-
false, true);
15678+
CreateTriggerFiringOn(trigStmt, NULL, RelationGetRelid(partition),
15679+
trigForm->tgconstrrelid, InvalidOid, InvalidOid,
15680+
trigForm->tgfoid, HeapTupleGetOid(tuple), qual,
15681+
false, true, trigForm->tgenabled);
1568215682

1568315683
MemoryContextSwitchTo(oldcxt);
1568415684
MemoryContextReset(perTupCxt);

‎src/backend/commands/trigger.c

Lines changed: 24 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -151,6 +151,24 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
151151
Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid,
152152
Oid funcoid, Oid parentTriggerOid, Node *whenClause,
153153
bool isInternal, bool in_partition)
154+
{
155+
return
156+
CreateTriggerFiringOn(stmt, queryString, relOid, refRelOid,
157+
constraintOid, indexOid, funcoid,
158+
parentTriggerOid, whenClause, isInternal,
159+
in_partition, TRIGGER_FIRES_ON_ORIGIN);
160+
}
161+
162+
/*
163+
* Like the above; additionally the firing condition
164+
* (always/origin/replica/disabled) can be specified.
165+
*/
166+
ObjectAddress
167+
CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
168+
Oid relOid, Oid refRelOid, Oid constraintOid,
169+
Oid indexOid, Oid funcoid, Oid parentTriggerOid,
170+
Node *whenClause, bool isInternal, bool in_partition,
171+
char trigger_fires_when)
154172
{
155173
int16 tgtype;
156174
int ncolumns;
@@ -819,7 +837,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
819837
CStringGetDatum(trigname));
820838
values[Anum_pg_trigger_tgfoid - 1] = ObjectIdGetDatum(funcoid);
821839
values[Anum_pg_trigger_tgtype - 1] = Int16GetDatum(tgtype);
822-
values[Anum_pg_trigger_tgenabled - 1] = CharGetDatum(TRIGGER_FIRES_ON_ORIGIN);
840+
values[Anum_pg_trigger_tgenabled - 1] = trigger_fires_when;
823841
values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal || in_partition);
824842
values[Anum_pg_trigger_tgconstrrelid - 1] = ObjectIdGetDatum(constrrelid);
825843
values[Anum_pg_trigger_tgconstrindid - 1] = ObjectIdGetDatum(indexOid);
@@ -1161,11 +1179,11 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
11611179
if (found_whole_row)
11621180
elog(ERROR, "unexpected whole-row reference found in trigger WHEN clause");
11631181

1164-
CreateTrigger(childStmt, queryString,
1165-
partdesc->oids[i], refRelOid,
1166-
InvalidOid, indexOnChild,
1167-
funcoid, trigoid, qual,
1168-
isInternal, true);
1182+
CreateTriggerFiringOn(childStmt, queryString,
1183+
partdesc->oids[i], refRelOid,
1184+
InvalidOid, indexOnChild,
1185+
funcoid, trigoid, qual,
1186+
isInternal, true, trigger_fires_when);
11691187

11701188
heap_close(childTbl, NoLock);
11711189

‎src/include/commands/trigger.h

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -161,6 +161,11 @@ extern ObjectAddress CreateTrigger(CreateTrigStmt *stmt, const char *queryString
161161
Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid,
162162
Oid funcoid, Oid parentTriggerOid, Node *whenClause,
163163
bool isInternal, bool in_partition);
164+
extern ObjectAddress CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
165+
Oid relOid, Oid refRelOid, Oid constraintOid,
166+
Oid indexOid, Oid funcoid, Oid parentTriggerOid,
167+
Node *whenClause, bool isInternal, bool in_partition,
168+
char trigger_fires_when);
164169

165170
extern void RemoveTriggerById(Oid trigOid);
166171
extern Oid get_trigger_oid(Oid relid, const char *name, bool missing_ok);

‎src/test/regress/expected/triggers.out

Lines changed: 56 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2496,6 +2496,62 @@ select tgrelid::regclass, tgname, tgenabled from pg_trigger
24962496
(2 rows)
24972497

24982498
drop table parent, child1;
2499+
-- Verify that firing state propagates correctly
2500+
CREATE TABLE trgfire (i int) PARTITION BY RANGE (i);
2501+
CREATE TABLE trgfire1 PARTITION OF trgfire FOR VALUES FROM (1) TO (10);
2502+
CREATE OR REPLACE FUNCTION tgf() RETURNS trigger LANGUAGE plpgsql
2503+
AS $$ begin raise exception 'except'; end $$;
2504+
CREATE TRIGGER tg AFTER INSERT ON trgfire FOR EACH ROW EXECUTE FUNCTION tgf();
2505+
INSERT INTO trgfire VALUES (1);
2506+
ERROR: except
2507+
CONTEXT: PL/pgSQL function tgf() line 1 at RAISE
2508+
ALTER TABLE trgfire DISABLE TRIGGER tg;
2509+
INSERT INTO trgfire VALUES (1);
2510+
CREATE TABLE trgfire2 PARTITION OF trgfire FOR VALUES FROM (10) TO (20);
2511+
INSERT INTO trgfire VALUES (11);
2512+
CREATE TABLE trgfire3 (LIKE trgfire);
2513+
ALTER TABLE trgfire ATTACH PARTITION trgfire3 FOR VALUES FROM (20) TO (30);
2514+
INSERT INTO trgfire VALUES (21);
2515+
CREATE TABLE trgfire4 PARTITION OF trgfire FOR VALUES FROM (30) TO (40) PARTITION BY LIST (i);
2516+
CREATE TABLE trgfire4_30 PARTITION OF trgfire4 FOR VALUES IN (30);
2517+
INSERT INTO trgfire VALUES (30);
2518+
CREATE TABLE trgfire5 (LIKE trgfire) PARTITION BY LIST (i);
2519+
CREATE TABLE trgfire5_40 PARTITION OF trgfire5 FOR VALUES IN (40);
2520+
ALTER TABLE trgfire ATTACH PARTITION trgfire5 FOR VALUES FROM (40) TO (50);
2521+
INSERT INTO trgfire VALUES (40);
2522+
SELECT tgrelid::regclass, tgenabled FROM pg_trigger
2523+
WHERE tgrelid::regclass IN (SELECT oid from pg_class where relname LIKE 'trgfire%')
2524+
ORDER BY tgrelid::regclass::text;
2525+
tgrelid | tgenabled
2526+
-------------+-----------
2527+
trgfire | D
2528+
trgfire1 | D
2529+
trgfire2 | D
2530+
trgfire3 | D
2531+
trgfire4 | D
2532+
trgfire4_30 | D
2533+
trgfire5 | D
2534+
trgfire5_40 | D
2535+
(8 rows)
2536+
2537+
ALTER TABLE trgfire ENABLE TRIGGER tg;
2538+
INSERT INTO trgfire VALUES (1);
2539+
ERROR: except
2540+
CONTEXT: PL/pgSQL function tgf() line 1 at RAISE
2541+
INSERT INTO trgfire VALUES (11);
2542+
ERROR: except
2543+
CONTEXT: PL/pgSQL function tgf() line 1 at RAISE
2544+
INSERT INTO trgfire VALUES (21);
2545+
ERROR: except
2546+
CONTEXT: PL/pgSQL function tgf() line 1 at RAISE
2547+
INSERT INTO trgfire VALUES (30);
2548+
ERROR: except
2549+
CONTEXT: PL/pgSQL function tgf() line 1 at RAISE
2550+
INSERT INTO trgfire VALUES (40);
2551+
ERROR: except
2552+
CONTEXT: PL/pgSQL function tgf() line 1 at RAISE
2553+
DROP TABLE trgfire;
2554+
DROP FUNCTION tgf();
24992555
--
25002556
-- Test the interaction between transition tables and both kinds of
25012557
-- inheritance. We'll dump the contents of the transition tables in a

‎src/test/regress/sql/triggers.sql

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1737,6 +1737,38 @@ select tgrelid::regclass, tgname, tgenabled from pg_trigger
17371737
order by tgrelid::regclass::text;
17381738
drop table parent, child1;
17391739

1740+
-- Verify that firing state propagates correctly
1741+
CREATE TABLE trgfire (i int) PARTITION BY RANGE (i);
1742+
CREATE TABLE trgfire1 PARTITION OF trgfire FOR VALUES FROM (1) TO (10);
1743+
CREATE OR REPLACE FUNCTION tgf() RETURNS trigger LANGUAGE plpgsql
1744+
AS $$ begin raise exception 'except'; end $$;
1745+
CREATE TRIGGER tg AFTER INSERT ON trgfire FOR EACH ROW EXECUTE FUNCTION tgf();
1746+
INSERT INTO trgfire VALUES (1);
1747+
ALTER TABLE trgfire DISABLE TRIGGER tg;
1748+
INSERT INTO trgfire VALUES (1);
1749+
CREATE TABLE trgfire2 PARTITION OF trgfire FOR VALUES FROM (10) TO (20);
1750+
INSERT INTO trgfire VALUES (11);
1751+
CREATE TABLE trgfire3 (LIKE trgfire);
1752+
ALTER TABLE trgfire ATTACH PARTITION trgfire3 FOR VALUES FROM (20) TO (30);
1753+
INSERT INTO trgfire VALUES (21);
1754+
CREATE TABLE trgfire4 PARTITION OF trgfire FOR VALUES FROM (30) TO (40) PARTITION BY LIST (i);
1755+
CREATE TABLE trgfire4_30 PARTITION OF trgfire4 FOR VALUES IN (30);
1756+
INSERT INTO trgfire VALUES (30);
1757+
CREATE TABLE trgfire5 (LIKE trgfire) PARTITION BY LIST (i);
1758+
CREATE TABLE trgfire5_40 PARTITION OF trgfire5 FOR VALUES IN (40);
1759+
ALTER TABLE trgfire ATTACH PARTITION trgfire5 FOR VALUES FROM (40) TO (50);
1760+
INSERT INTO trgfire VALUES (40);
1761+
SELECT tgrelid::regclass, tgenabled FROM pg_trigger
1762+
WHERE tgrelid::regclass IN (SELECT oid from pg_class where relname LIKE 'trgfire%')
1763+
ORDER BY tgrelid::regclass::text;
1764+
ALTER TABLE trgfire ENABLE TRIGGER tg;
1765+
INSERT INTO trgfire VALUES (1);
1766+
INSERT INTO trgfire VALUES (11);
1767+
INSERT INTO trgfire VALUES (21);
1768+
INSERT INTO trgfire VALUES (30);
1769+
INSERT INTO trgfire VALUES (40);
1770+
DROP TABLE trgfire;
1771+
DROP FUNCTION tgf();
17401772

17411773
--
17421774
-- Test the interaction between transition tables and both kinds of

0 commit comments

Comments
 (0)