Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit2ecfeda

Browse files
committed
Add more tests with triggers on partitions for logical replication
The tuple routing logic used by a logical replication worker can firetriggers on relations part of a partition tree, but there was no testcoverage in this area. The existing script 003_constraints.pl includedsomething, but nothing when a tuple is applied across partitioned tableson a subscriber.Author: Amit LangoteDiscussion:https://postgr.es/m/OS0PR01MB611383FA0FE92EB9DE21946AFB769@OS0PR01MB6113.jpnprd01.prod.outlook.com
1 parentf25a458 commit2ecfeda

File tree

1 file changed

+90
-1
lines changed

1 file changed

+90
-1
lines changed

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

Lines changed: 90 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
use warnings;
44
use PostgresNode;
55
use TestLib;
6-
use Test::Moretests=>51;
6+
use Test::Moretests=>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)
[8]ページ先頭
©2009-2025 Movatter.jp