Skip to content

Commit df80fa2

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 ead9e51 commit df80fa2

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
@@ -17732,10 +17732,10 @@ CloneRowTriggersToPartition(Relation parent, Relation partition)
1773217732
trigStmt->initdeferred = trigForm->tginitdeferred;
1773317733
trigStmt->constrrel = NULL; /* passed separately */
1773417734

17735-
CreateTrigger(trigStmt, NULL, RelationGetRelid(partition),
17736-
trigForm->tgconstrrelid, InvalidOid, InvalidOid,
17737-
trigForm->tgfoid, trigForm->oid, qual,
17738-
false, true);
17735+
CreateTriggerFiringOn(trigStmt, NULL, RelationGetRelid(partition),
17736+
trigForm->tgconstrrelid, InvalidOid, InvalidOid,
17737+
trigForm->tgfoid, trigForm->oid, qual,
17738+
false, true, trigForm->tgenabled);
1773917739

1774017740
MemoryContextSwitchTo(oldcxt);
1774117741
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;
@@ -849,7 +867,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
849867
CStringGetDatum(trigname));
850868
values[Anum_pg_trigger_tgfoid - 1] = ObjectIdGetDatum(funcoid);
851869
values[Anum_pg_trigger_tgtype - 1] = Int16GetDatum(tgtype);
852-
values[Anum_pg_trigger_tgenabled - 1] = CharGetDatum(TRIGGER_FIRES_ON_ORIGIN);
870+
values[Anum_pg_trigger_tgenabled - 1] = trigger_fires_when;
853871
values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal || in_partition);
854872
values[Anum_pg_trigger_tgconstrrelid - 1] = ObjectIdGetDatum(constrrelid);
855873
values[Anum_pg_trigger_tgconstrindid - 1] = ObjectIdGetDatum(indexOid);
@@ -1196,11 +1214,11 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
11961214
map_partition_varattnos((List *) qual, PRS2_NEW_VARNO,
11971215
childTbl, rel);
11981216

1199-
CreateTrigger(childStmt, queryString,
1200-
partdesc->oids[i], refRelOid,
1201-
InvalidOid, indexOnChild,
1202-
funcoid, trigoid, qual,
1203-
isInternal, true);
1217+
CreateTriggerFiringOn(childStmt, queryString,
1218+
partdesc->oids[i], refRelOid,
1219+
InvalidOid, indexOnChild,
1220+
funcoid, trigoid, qual,
1221+
isInternal, true, trigger_fires_when);
12041222

12051223
table_close(childTbl, NoLock);
12061224

‎src/include/commands/trigger.h

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -154,6 +154,11 @@ extern ObjectAddress CreateTrigger(CreateTrigStmt *stmt, const char *queryString
154154
Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid,
155155
Oid funcoid, Oid parentTriggerOid, Node *whenClause,
156156
bool isInternal, bool in_partition);
157+
extern ObjectAddress CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
158+
Oid relOid, Oid refRelOid, Oid constraintOid,
159+
Oid indexOid, Oid funcoid, Oid parentTriggerOid,
160+
Node *whenClause, bool isInternal, bool in_partition,
161+
char trigger_fires_when);
157162

158163
extern void RemoveTriggerById(Oid trigOid);
159164
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
@@ -2661,6 +2661,62 @@ select tgrelid::regclass, tgname, tgenabled from pg_trigger
26612661
(2 rows)
26622662

26632663
drop table parent, child1;
2664+
-- Verify that firing state propagates correctly on creation, too
2665+
CREATE TABLE trgfire (i int) PARTITION BY RANGE (i);
2666+
CREATE TABLE trgfire1 PARTITION OF trgfire FOR VALUES FROM (1) TO (10);
2667+
CREATE OR REPLACE FUNCTION tgf() RETURNS trigger LANGUAGE plpgsql
2668+
AS $$ begin raise exception 'except'; end $$;
2669+
CREATE TRIGGER tg AFTER INSERT ON trgfire FOR EACH ROW EXECUTE FUNCTION tgf();
2670+
INSERT INTO trgfire VALUES (1);
2671+
ERROR: except
2672+
CONTEXT: PL/pgSQL function tgf() line 1 at RAISE
2673+
ALTER TABLE trgfire DISABLE TRIGGER tg;
2674+
INSERT INTO trgfire VALUES (1);
2675+
CREATE TABLE trgfire2 PARTITION OF trgfire FOR VALUES FROM (10) TO (20);
2676+
INSERT INTO trgfire VALUES (11);
2677+
CREATE TABLE trgfire3 (LIKE trgfire);
2678+
ALTER TABLE trgfire ATTACH PARTITION trgfire3 FOR VALUES FROM (20) TO (30);
2679+
INSERT INTO trgfire VALUES (21);
2680+
CREATE TABLE trgfire4 PARTITION OF trgfire FOR VALUES FROM (30) TO (40) PARTITION BY LIST (i);
2681+
CREATE TABLE trgfire4_30 PARTITION OF trgfire4 FOR VALUES IN (30);
2682+
INSERT INTO trgfire VALUES (30);
2683+
CREATE TABLE trgfire5 (LIKE trgfire) PARTITION BY LIST (i);
2684+
CREATE TABLE trgfire5_40 PARTITION OF trgfire5 FOR VALUES IN (40);
2685+
ALTER TABLE trgfire ATTACH PARTITION trgfire5 FOR VALUES FROM (40) TO (50);
2686+
INSERT INTO trgfire VALUES (40);
2687+
SELECT tgrelid::regclass, tgenabled FROM pg_trigger
2688+
WHERE tgrelid::regclass IN (SELECT oid from pg_class where relname LIKE 'trgfire%')
2689+
ORDER BY tgrelid::regclass::text;
2690+
tgrelid | tgenabled
2691+
-------------+-----------
2692+
trgfire | D
2693+
trgfire1 | D
2694+
trgfire2 | D
2695+
trgfire3 | D
2696+
trgfire4 | D
2697+
trgfire4_30 | D
2698+
trgfire5 | D
2699+
trgfire5_40 | D
2700+
(8 rows)
2701+
2702+
ALTER TABLE trgfire ENABLE TRIGGER tg;
2703+
INSERT INTO trgfire VALUES (1);
2704+
ERROR: except
2705+
CONTEXT: PL/pgSQL function tgf() line 1 at RAISE
2706+
INSERT INTO trgfire VALUES (11);
2707+
ERROR: except
2708+
CONTEXT: PL/pgSQL function tgf() line 1 at RAISE
2709+
INSERT INTO trgfire VALUES (21);
2710+
ERROR: except
2711+
CONTEXT: PL/pgSQL function tgf() line 1 at RAISE
2712+
INSERT INTO trgfire VALUES (30);
2713+
ERROR: except
2714+
CONTEXT: PL/pgSQL function tgf() line 1 at RAISE
2715+
INSERT INTO trgfire VALUES (40);
2716+
ERROR: except
2717+
CONTEXT: PL/pgSQL function tgf() line 1 at RAISE
2718+
DROP TABLE trgfire;
2719+
DROP FUNCTION tgf();
26642720
--
26652721
-- Test the interaction between transition tables and both kinds of
26662722
-- 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
@@ -1836,6 +1836,38 @@ select tgrelid::regclass, tgname, tgenabled from pg_trigger
18361836
order by tgrelid::regclass::text;
18371837
drop table parent, child1;
18381838

1839+
-- Verify that firing state propagates correctly on creation, too
1840+
CREATE TABLE trgfire (i int) PARTITION BY RANGE (i);
1841+
CREATE TABLE trgfire1 PARTITION OF trgfire FOR VALUES FROM (1) TO (10);
1842+
CREATE OR REPLACE FUNCTION tgf() RETURNS trigger LANGUAGE plpgsql
1843+
AS $$ begin raise exception 'except'; end $$;
1844+
CREATE TRIGGER tg AFTER INSERT ON trgfire FOR EACH ROW EXECUTE FUNCTION tgf();
1845+
INSERT INTO trgfire VALUES (1);
1846+
ALTER TABLE trgfire DISABLE TRIGGER tg;
1847+
INSERT INTO trgfire VALUES (1);
1848+
CREATE TABLE trgfire2 PARTITION OF trgfire FOR VALUES FROM (10) TO (20);
1849+
INSERT INTO trgfire VALUES (11);
1850+
CREATE TABLE trgfire3 (LIKE trgfire);
1851+
ALTER TABLE trgfire ATTACH PARTITION trgfire3 FOR VALUES FROM (20) TO (30);
1852+
INSERT INTO trgfire VALUES (21);
1853+
CREATE TABLE trgfire4 PARTITION OF trgfire FOR VALUES FROM (30) TO (40) PARTITION BY LIST (i);
1854+
CREATE TABLE trgfire4_30 PARTITION OF trgfire4 FOR VALUES IN (30);
1855+
INSERT INTO trgfire VALUES (30);
1856+
CREATE TABLE trgfire5 (LIKE trgfire) PARTITION BY LIST (i);
1857+
CREATE TABLE trgfire5_40 PARTITION OF trgfire5 FOR VALUES IN (40);
1858+
ALTER TABLE trgfire ATTACH PARTITION trgfire5 FOR VALUES FROM (40) TO (50);
1859+
INSERT INTO trgfire VALUES (40);
1860+
SELECT tgrelid::regclass, tgenabled FROM pg_trigger
1861+
WHERE tgrelid::regclass IN (SELECT oid from pg_class where relname LIKE 'trgfire%')
1862+
ORDER BY tgrelid::regclass::text;
1863+
ALTER TABLE trgfire ENABLE TRIGGER tg;
1864+
INSERT INTO trgfire VALUES (1);
1865+
INSERT INTO trgfire VALUES (11);
1866+
INSERT INTO trgfire VALUES (21);
1867+
INSERT INTO trgfire VALUES (30);
1868+
INSERT INTO trgfire VALUES (40);
1869+
DROP TABLE trgfire;
1870+
DROP FUNCTION tgf();
18391871

18401872
--
18411873
-- Test the interaction between transition tables and both kinds of

0 commit comments

Comments
 (0)