Skip to content

Commit 2ecfeda

Browse files
michaelpqmichaelpq
committed
Add more tests with triggers on partitions for logical replication
The tuple routing logic used by a logical replication worker can fire triggers on relations part of a partition tree, but there was no test coverage in this area. The existing script 003_constraints.pl included something, but nothing when a tuple is applied across partitioned tables on a subscriber. Author: Amit Langote Discussion: https://postgr.es/m/OS0PR01MB611383FA0FE92EB9DE21946AFB769@OS0PR01MB6113.jpnprd01.prod.outlook.com
1 parent f25a458 commit 2ecfeda

File tree

1 file changed

+90
-1
lines changed

1 file changed

+90
-1
lines changed

‎src/test/subscription/t/013_partition.pl

+90-1
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
use warnings;
44
use PostgresNode;
55
use TestLib;
6-
use Test::More tests => 51;
6+
use Test::More tests => 54;
77

88
# setup
99

@@ -67,6 +67,40 @@
6767
"CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub1"
6868
);
6969

70+
# Add set of AFTER replica triggers for testing that they are fired
71+
# correctly. This uses a table that records details of all trigger
72+
# activities. Triggers are marked as enabled for a subset of the
73+
# partition tree.
74+
$node_subscriber1->safe_psql(
75+
'postgres', qq{
76+
CREATE TABLE sub1_trigger_activity (tgtab text, tgop text,
77+
tgwhen text, tglevel text, olda int, newa int);
78+
CREATE FUNCTION sub1_trigger_activity_func() RETURNS TRIGGER AS \$\$
79+
BEGIN
80+
IF (TG_OP = 'INSERT') THEN
81+
INSERT INTO public.sub1_trigger_activity
82+
SELECT TG_RELNAME, TG_OP, TG_WHEN, TG_LEVEL, NULL, NEW.a;
83+
ELSIF (TG_OP = 'UPDATE') THEN
84+
INSERT INTO public.sub1_trigger_activity
85+
SELECT TG_RELNAME, TG_OP, TG_WHEN, TG_LEVEL, OLD.a, NEW.a;
86+
END IF;
87+
RETURN NULL;
88+
END;
89+
\$\$ LANGUAGE plpgsql;
90+
CREATE TRIGGER sub1_tab1_log_op_trigger
91+
AFTER INSERT OR UPDATE ON tab1
92+
FOR EACH ROW EXECUTE PROCEDURE sub1_trigger_activity_func();
93+
ALTER TABLE ONLY tab1 ENABLE REPLICA TRIGGER sub1_tab1_log_op_trigger;
94+
CREATE TRIGGER sub1_tab1_2_log_op_trigger
95+
AFTER INSERT OR UPDATE ON tab1_2
96+
FOR EACH ROW EXECUTE PROCEDURE sub1_trigger_activity_func();
97+
ALTER TABLE ONLY tab1_2 ENABLE REPLICA TRIGGER sub1_tab1_2_log_op_trigger;
98+
CREATE TRIGGER sub1_tab1_2_2_log_op_trigger
99+
AFTER INSERT OR UPDATE ON tab1_2_2
100+
FOR EACH ROW EXECUTE PROCEDURE sub1_trigger_activity_func();
101+
ALTER TABLE ONLY tab1_2_2 ENABLE REPLICA TRIGGER sub1_tab1_2_2_log_op_trigger;
102+
});
103+
70104
# subscriber 2
71105
#
72106
# This does not use partitioning. The tables match the leaf tables on
@@ -87,6 +121,34 @@
87121
"CREATE SUBSCRIPTION sub2 CONNECTION '$publisher_connstr' PUBLICATION pub_all"
88122
);
89123

124+
# Add set of AFTER replica triggers for testing that they are fired
125+
# correctly, using the same method as the first subscriber.
126+
$node_subscriber2->safe_psql(
127+
'postgres', qq{
128+
CREATE TABLE sub2_trigger_activity (tgtab text,
129+
tgop text, tgwhen text, tglevel text, olda int, newa int);
130+
CREATE FUNCTION sub2_trigger_activity_func() RETURNS TRIGGER AS \$\$
131+
BEGIN
132+
IF (TG_OP = 'INSERT') THEN
133+
INSERT INTO public.sub2_trigger_activity
134+
SELECT TG_RELNAME, TG_OP, TG_WHEN, TG_LEVEL, NULL, NEW.a;
135+
ELSIF (TG_OP = 'UPDATE') THEN
136+
INSERT INTO public.sub2_trigger_activity
137+
SELECT TG_RELNAME, TG_OP, TG_WHEN, TG_LEVEL, OLD.a, NEW.a;
138+
END IF;
139+
RETURN NULL;
140+
END;
141+
\$\$ LANGUAGE plpgsql;
142+
CREATE TRIGGER sub2_tab1_log_op_trigger
143+
AFTER INSERT OR UPDATE ON tab1
144+
FOR EACH ROW EXECUTE PROCEDURE sub2_trigger_activity_func();
145+
ALTER TABLE ONLY tab1 ENABLE REPLICA TRIGGER sub2_tab1_log_op_trigger;
146+
CREATE TRIGGER sub2_tab1_2_log_op_trigger
147+
AFTER INSERT OR UPDATE ON tab1_2
148+
FOR EACH ROW EXECUTE PROCEDURE sub2_trigger_activity_func();
149+
ALTER TABLE ONLY tab1_2 ENABLE REPLICA TRIGGER sub2_tab1_2_log_op_trigger;
150+
});
151+
90152
# Wait for initial sync of all subscriptions
91153
my $synced_query =
92154
"SELECT count(1) = 0 FROM pg_subscription_rel WHERE srsubstate NOT IN ('r', 's');";
@@ -130,6 +192,14 @@
130192
"SELECT c, a FROM tab1_2 ORDER BY 1, 2");
131193
is($result, qq(sub2_tab1_2|5), 'inserts into tab1_2 replicated');
132194

195+
# The AFTER trigger of tab1_2 should have recorded one INSERT.
196+
$result = $node_subscriber2->safe_psql('postgres',
197+
"SELECT * FROM sub2_trigger_activity ORDER BY tgtab, tgop, tgwhen, olda, newa;"
198+
);
199+
is( $result,
200+
qq(tab1_2|INSERT|AFTER|ROW||5),
201+
'check replica insert after trigger applied on subscriber');
202+
133203
$result = $node_subscriber2->safe_psql('postgres',
134204
"SELECT c, a FROM tab1_def ORDER BY 1, 2");
135205
is($result, qq(sub2_tab1_def|0), 'inserts into tab1_def replicated');
@@ -161,6 +231,15 @@
161231
"SELECT a FROM tab1_2_2 ORDER BY 1");
162232
is($result, qq(6), 'updates of tab1_2 replicated into tab1_2_2 correctly');
163233

234+
# The AFTER trigger should have recorded the UPDATEs of tab1_2_2.
235+
$result = $node_subscriber1->safe_psql('postgres',
236+
"SELECT * FROM sub1_trigger_activity ORDER BY tgtab, tgop, tgwhen, olda, newa;"
237+
);
238+
is( $result, qq(tab1_2_2|INSERT|AFTER|ROW||6
239+
tab1_2_2|UPDATE|AFTER|ROW|4|6
240+
tab1_2_2|UPDATE|AFTER|ROW|6|4),
241+
'check replica update after trigger applied on subscriber');
242+
164243
$result = $node_subscriber2->safe_psql('postgres',
165244
"SELECT c, a FROM tab1_1 ORDER BY 1, 2");
166245
is( $result, qq(sub2_tab1_1|2
@@ -170,6 +249,16 @@
170249
"SELECT c, a FROM tab1_2 ORDER BY 1, 2");
171250
is($result, qq(sub2_tab1_2|6), 'tab1_2 updated');
172251

252+
# The AFTER trigger should have recorded the updates of tab1_2.
253+
$result = $node_subscriber2->safe_psql('postgres',
254+
"SELECT * FROM sub2_trigger_activity ORDER BY tgtab, tgop, tgwhen, olda, newa;"
255+
);
256+
is( $result, qq(tab1_2|INSERT|AFTER|ROW||5
257+
tab1_2|UPDATE|AFTER|ROW|4|6
258+
tab1_2|UPDATE|AFTER|ROW|5|6
259+
tab1_2|UPDATE|AFTER|ROW|6|4),
260+
'check replica update after trigger applied on subscriber');
261+
173262
$result = $node_subscriber2->safe_psql('postgres',
174263
"SELECT c, a FROM tab1_def ORDER BY 1");
175264
is($result, qq(sub2_tab1_def|0), 'tab1_def unchanged');

0 commit comments

Comments
 (0)