Skip to content

Commit 7584ec1

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 b242e1d commit 7584ec1

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
@@ -16688,10 +16688,10 @@ CloneRowTriggersToPartition(Relation parent, Relation partition)
1668816688
trigStmt->initdeferred = trigForm->tginitdeferred;
1668916689
trigStmt->constrrel = NULL; /* passed separately */
1669016690

16691-
CreateTrigger(trigStmt, NULL, RelationGetRelid(partition),
16692-
trigForm->tgconstrrelid, InvalidOid, InvalidOid,
16693-
trigForm->tgfoid, trigForm->oid, qual,
16694-
false, true);
16691+
CreateTriggerFiringOn(trigStmt, NULL, RelationGetRelid(partition),
16692+
trigForm->tgconstrrelid, InvalidOid, InvalidOid,
16693+
trigForm->tgfoid, trigForm->oid, qual,
16694+
false, true, trigForm->tgenabled);
1669516695

1669616696
MemoryContextSwitchTo(oldcxt);
1669716697
MemoryContextReset(perTupCxt);

‎src/backend/commands/trigger.c

Lines changed: 24 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -153,6 +153,24 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
153153
Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid,
154154
Oid funcoid, Oid parentTriggerOid, Node *whenClause,
155155
bool isInternal, bool in_partition)
156+
{
157+
return
158+
CreateTriggerFiringOn(stmt, queryString, relOid, refRelOid,
159+
constraintOid, indexOid, funcoid,
160+
parentTriggerOid, whenClause, isInternal,
161+
in_partition, TRIGGER_FIRES_ON_ORIGIN);
162+
}
163+
164+
/*
165+
* Like the above; additionally the firing condition
166+
* (always/origin/replica/disabled) can be specified.
167+
*/
168+
ObjectAddress
169+
CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
170+
Oid relOid, Oid refRelOid, Oid constraintOid,
171+
Oid indexOid, Oid funcoid, Oid parentTriggerOid,
172+
Node *whenClause, bool isInternal, bool in_partition,
173+
char trigger_fires_when)
156174
{
157175
int16 tgtype;
158176
int ncolumns;
@@ -842,7 +860,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
842860
CStringGetDatum(trigname));
843861
values[Anum_pg_trigger_tgfoid - 1] = ObjectIdGetDatum(funcoid);
844862
values[Anum_pg_trigger_tgtype - 1] = Int16GetDatum(tgtype);
845-
values[Anum_pg_trigger_tgenabled - 1] = CharGetDatum(TRIGGER_FIRES_ON_ORIGIN);
863+
values[Anum_pg_trigger_tgenabled - 1] = trigger_fires_when;
846864
values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal || in_partition);
847865
values[Anum_pg_trigger_tgconstrrelid - 1] = ObjectIdGetDatum(constrrelid);
848866
values[Anum_pg_trigger_tgconstrindid - 1] = ObjectIdGetDatum(indexOid);
@@ -1179,11 +1197,11 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
11791197
if (found_whole_row)
11801198
elog(ERROR, "unexpected whole-row reference found in trigger WHEN clause");
11811199

1182-
CreateTrigger(childStmt, queryString,
1183-
partdesc->oids[i], refRelOid,
1184-
InvalidOid, indexOnChild,
1185-
funcoid, trigoid, qual,
1186-
isInternal, true);
1200+
CreateTriggerFiringOn(childStmt, queryString,
1201+
partdesc->oids[i], refRelOid,
1202+
InvalidOid, indexOnChild,
1203+
funcoid, trigoid, qual,
1204+
isInternal, true, trigger_fires_when);
11871205

11881206
table_close(childTbl, NoLock);
11891207

‎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
@@ -2552,6 +2552,62 @@ select tgrelid::regclass, tgname, tgenabled from pg_trigger
25522552
(2 rows)
25532553

25542554
drop table parent, child1;
2555+
-- Verify that firing state propagates correctly on creation, too
2556+
CREATE TABLE trgfire (i int) PARTITION BY RANGE (i);
2557+
CREATE TABLE trgfire1 PARTITION OF trgfire FOR VALUES FROM (1) TO (10);
2558+
CREATE OR REPLACE FUNCTION tgf() RETURNS trigger LANGUAGE plpgsql
2559+
AS $$ begin raise exception 'except'; end $$;
2560+
CREATE TRIGGER tg AFTER INSERT ON trgfire FOR EACH ROW EXECUTE FUNCTION tgf();
2561+
INSERT INTO trgfire VALUES (1);
2562+
ERROR: except
2563+
CONTEXT: PL/pgSQL function tgf() line 1 at RAISE
2564+
ALTER TABLE trgfire DISABLE TRIGGER tg;
2565+
INSERT INTO trgfire VALUES (1);
2566+
CREATE TABLE trgfire2 PARTITION OF trgfire FOR VALUES FROM (10) TO (20);
2567+
INSERT INTO trgfire VALUES (11);
2568+
CREATE TABLE trgfire3 (LIKE trgfire);
2569+
ALTER TABLE trgfire ATTACH PARTITION trgfire3 FOR VALUES FROM (20) TO (30);
2570+
INSERT INTO trgfire VALUES (21);
2571+
CREATE TABLE trgfire4 PARTITION OF trgfire FOR VALUES FROM (30) TO (40) PARTITION BY LIST (i);
2572+
CREATE TABLE trgfire4_30 PARTITION OF trgfire4 FOR VALUES IN (30);
2573+
INSERT INTO trgfire VALUES (30);
2574+
CREATE TABLE trgfire5 (LIKE trgfire) PARTITION BY LIST (i);
2575+
CREATE TABLE trgfire5_40 PARTITION OF trgfire5 FOR VALUES IN (40);
2576+
ALTER TABLE trgfire ATTACH PARTITION trgfire5 FOR VALUES FROM (40) TO (50);
2577+
INSERT INTO trgfire VALUES (40);
2578+
SELECT tgrelid::regclass, tgenabled FROM pg_trigger
2579+
WHERE tgrelid::regclass IN (SELECT oid from pg_class where relname LIKE 'trgfire%')
2580+
ORDER BY tgrelid::regclass::text;
2581+
tgrelid | tgenabled
2582+
-------------+-----------
2583+
trgfire | D
2584+
trgfire1 | D
2585+
trgfire2 | D
2586+
trgfire3 | D
2587+
trgfire4 | D
2588+
trgfire4_30 | D
2589+
trgfire5 | D
2590+
trgfire5_40 | D
2591+
(8 rows)
2592+
2593+
ALTER TABLE trgfire ENABLE TRIGGER tg;
2594+
INSERT INTO trgfire VALUES (1);
2595+
ERROR: except
2596+
CONTEXT: PL/pgSQL function tgf() line 1 at RAISE
2597+
INSERT INTO trgfire VALUES (11);
2598+
ERROR: except
2599+
CONTEXT: PL/pgSQL function tgf() line 1 at RAISE
2600+
INSERT INTO trgfire VALUES (21);
2601+
ERROR: except
2602+
CONTEXT: PL/pgSQL function tgf() line 1 at RAISE
2603+
INSERT INTO trgfire VALUES (30);
2604+
ERROR: except
2605+
CONTEXT: PL/pgSQL function tgf() line 1 at RAISE
2606+
INSERT INTO trgfire VALUES (40);
2607+
ERROR: except
2608+
CONTEXT: PL/pgSQL function tgf() line 1 at RAISE
2609+
DROP TABLE trgfire;
2610+
DROP FUNCTION tgf();
25552611
--
25562612
-- Test the interaction between transition tables and both kinds of
25572613
-- 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
@@ -1759,6 +1759,38 @@ select tgrelid::regclass, tgname, tgenabled from pg_trigger
17591759
order by tgrelid::regclass::text;
17601760
drop table parent, child1;
17611761

1762+
-- Verify that firing state propagates correctly on creation, too
1763+
CREATE TABLE trgfire (i int) PARTITION BY RANGE (i);
1764+
CREATE TABLE trgfire1 PARTITION OF trgfire FOR VALUES FROM (1) TO (10);
1765+
CREATE OR REPLACE FUNCTION tgf() RETURNS trigger LANGUAGE plpgsql
1766+
AS $$ begin raise exception 'except'; end $$;
1767+
CREATE TRIGGER tg AFTER INSERT ON trgfire FOR EACH ROW EXECUTE FUNCTION tgf();
1768+
INSERT INTO trgfire VALUES (1);
1769+
ALTER TABLE trgfire DISABLE TRIGGER tg;
1770+
INSERT INTO trgfire VALUES (1);
1771+
CREATE TABLE trgfire2 PARTITION OF trgfire FOR VALUES FROM (10) TO (20);
1772+
INSERT INTO trgfire VALUES (11);
1773+
CREATE TABLE trgfire3 (LIKE trgfire);
1774+
ALTER TABLE trgfire ATTACH PARTITION trgfire3 FOR VALUES FROM (20) TO (30);
1775+
INSERT INTO trgfire VALUES (21);
1776+
CREATE TABLE trgfire4 PARTITION OF trgfire FOR VALUES FROM (30) TO (40) PARTITION BY LIST (i);
1777+
CREATE TABLE trgfire4_30 PARTITION OF trgfire4 FOR VALUES IN (30);
1778+
INSERT INTO trgfire VALUES (30);
1779+
CREATE TABLE trgfire5 (LIKE trgfire) PARTITION BY LIST (i);
1780+
CREATE TABLE trgfire5_40 PARTITION OF trgfire5 FOR VALUES IN (40);
1781+
ALTER TABLE trgfire ATTACH PARTITION trgfire5 FOR VALUES FROM (40) TO (50);
1782+
INSERT INTO trgfire VALUES (40);
1783+
SELECT tgrelid::regclass, tgenabled FROM pg_trigger
1784+
WHERE tgrelid::regclass IN (SELECT oid from pg_class where relname LIKE 'trgfire%')
1785+
ORDER BY tgrelid::regclass::text;
1786+
ALTER TABLE trgfire ENABLE TRIGGER tg;
1787+
INSERT INTO trgfire VALUES (1);
1788+
INSERT INTO trgfire VALUES (11);
1789+
INSERT INTO trgfire VALUES (21);
1790+
INSERT INTO trgfire VALUES (30);
1791+
INSERT INTO trgfire VALUES (40);
1792+
DROP TABLE trgfire;
1793+
DROP FUNCTION tgf();
17621794

17631795
--
17641796
-- Test the interaction between transition tables and both kinds of

0 commit comments

Comments
 (0)