Skip to content

Commit 0352c15

Browse files
simonat2ndQuadrantsimonat2ndQuadrant
committed
Additional tests for subtransactions in recovery
Tests for normal and prepared transactions Author: Nikhil Sontakke, placed in new test file by me
1 parent 6c9bd27 commit 0352c15

File tree

2 files changed

+198
-38
lines changed

2 files changed

+198
-38
lines changed

‎src/test/recovery/t/009_twophase.pl

+1-38
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44

55
use PostgresNode;
66
use TestLib;
7-
use Test::More tests => 13;
7+
use Test::More tests => 12;
88

99
# Setup master node
1010
my $node_master = get_new_node("master");
@@ -283,40 +283,3 @@
283283
$node_slave->psql('postgres', "SELECT count(*) FROM pg_prepared_xacts",
284284
stdout => \$psql_out);
285285
is($psql_out, '0', "Replay prepared transaction with DDL");
286-
287-
288-
###############################################################################
289-
# Check that replay will correctly set SUBTRANS and properly advance nextXid
290-
# so that it won't conflict with savepoint xids.
291-
###############################################################################
292-
293-
$node_master->psql('postgres', "
294-
BEGIN;
295-
DELETE FROM t_009_tbl;
296-
INSERT INTO t_009_tbl VALUES (43);
297-
SAVEPOINT s1;
298-
INSERT INTO t_009_tbl VALUES (43);
299-
SAVEPOINT s2;
300-
INSERT INTO t_009_tbl VALUES (43);
301-
SAVEPOINT s3;
302-
INSERT INTO t_009_tbl VALUES (43);
303-
SAVEPOINT s4;
304-
INSERT INTO t_009_tbl VALUES (43);
305-
SAVEPOINT s5;
306-
INSERT INTO t_009_tbl VALUES (43);
307-
PREPARE TRANSACTION 'xact_009_1';
308-
CHECKPOINT;");
309-
310-
$node_master->stop;
311-
$node_master->start;
312-
$node_master->psql('postgres', "
313-
-- here we can get xid of previous savepoint if nextXid
314-
-- wasn't properly advanced
315-
BEGIN;
316-
INSERT INTO t_009_tbl VALUES (142);
317-
ROLLBACK;
318-
COMMIT PREPARED 'xact_009_1';");
319-
320-
$node_master->psql('postgres', "SELECT count(*) FROM t_009_tbl",
321-
stdout => \$psql_out);
322-
is($psql_out, '6', "Check nextXid handling for prepared subtransactions");

‎src/test/recovery/t/012_subtransactions.pl

+197
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,197 @@
1+
# Tests dedicated to subtransactions in recovery
2+
use strict;
3+
use warnings;
4+
5+
use PostgresNode;
6+
use TestLib;
7+
use Test::More tests => 12;
8+
9+
# Setup master node
10+
my $node_master = get_new_node("master");
11+
$node_master->init(allows_streaming => 1);
12+
$node_master->append_conf('postgresql.conf', qq(
13+
max_prepared_transactions = 10
14+
log_checkpoints = true
15+
));
16+
$node_master->start;
17+
$node_master->backup('master_backup');
18+
$node_master->psql('postgres', "CREATE TABLE t_012_tbl (id int)");
19+
20+
# Setup slave node
21+
my $node_slave = get_new_node('slave');
22+
$node_slave->init_from_backup($node_master, 'master_backup', has_streaming => 1);
23+
$node_slave->start;
24+
25+
# Switch to synchronous replication
26+
$node_master->append_conf('postgresql.conf', qq(
27+
synchronous_standby_names = '*'
28+
));
29+
$node_master->psql('postgres', "SELECT pg_reload_conf()");
30+
31+
my $psql_out = '';
32+
my $psql_rc = '';
33+
34+
###############################################################################
35+
# Check that replay will correctly set SUBTRANS and properly advance nextXid
36+
# so that it won't conflict with savepoint xids.
37+
###############################################################################
38+
39+
$node_master->psql('postgres', "
40+
BEGIN;
41+
DELETE FROM t_012_tbl;
42+
INSERT INTO t_012_tbl VALUES (43);
43+
SAVEPOINT s1;
44+
INSERT INTO t_012_tbl VALUES (43);
45+
SAVEPOINT s2;
46+
INSERT INTO t_012_tbl VALUES (43);
47+
SAVEPOINT s3;
48+
INSERT INTO t_012_tbl VALUES (43);
49+
SAVEPOINT s4;
50+
INSERT INTO t_012_tbl VALUES (43);
51+
SAVEPOINT s5;
52+
INSERT INTO t_012_tbl VALUES (43);
53+
PREPARE TRANSACTION 'xact_012_1';
54+
CHECKPOINT;");
55+
56+
$node_master->stop;
57+
$node_master->start;
58+
$node_master->psql('postgres', "
59+
-- here we can get xid of previous savepoint if nextXid
60+
-- wasn't properly advanced
61+
BEGIN;
62+
INSERT INTO t_012_tbl VALUES (142);
63+
ROLLBACK;
64+
COMMIT PREPARED 'xact_012_1';");
65+
66+
$node_master->psql('postgres', "SELECT count(*) FROM t_012_tbl",
67+
stdout => \$psql_out);
68+
is($psql_out, '6', "Check nextXid handling for prepared subtransactions");
69+
70+
###############################################################################
71+
# Check that replay will correctly set 2PC with more than
72+
# PGPROC_MAX_CACHED_SUBXIDS subtransations and also show data properly
73+
# on promotion
74+
###############################################################################
75+
$node_master->psql('postgres', "DELETE FROM t_012_tbl");
76+
77+
# Function borrowed from src/test/regress/sql/hs_primary_extremes.sql
78+
$node_master->psql('postgres', "
79+
CREATE OR REPLACE FUNCTION hs_subxids (n integer)
80+
RETURNS void
81+
LANGUAGE plpgsql
82+
AS \$\$
83+
BEGIN
84+
IF n <= 0 THEN RETURN; END IF;
85+
INSERT INTO t_012_tbl VALUES (n);
86+
PERFORM hs_subxids(n - 1);
87+
RETURN;
88+
EXCEPTION WHEN raise_exception THEN NULL; END;
89+
\$\$;");
90+
$node_master->psql('postgres', "
91+
BEGIN;
92+
SELECT hs_subxids(127);
93+
COMMIT;");
94+
$node_master->wait_for_catchup($node_slave, 'replay', $node_master->lsn('insert'));
95+
$node_slave->psql('postgres', "SELECT coalesce(sum(id),-1) FROM t_012_tbl",
96+
stdout => \$psql_out);
97+
is($psql_out, '8128', "Visible");
98+
$node_master->stop;
99+
$node_slave->promote;
100+
$node_slave->poll_query_until('postgres',
101+
"SELECT NOT pg_is_in_recovery()")
102+
or die "Timed out while waiting for promotion of standby";
103+
104+
$node_slave->psql('postgres', "SELECT coalesce(sum(id),-1) FROM t_012_tbl",
105+
stdout => \$psql_out);
106+
is($psql_out, '8128', "Visible");
107+
108+
# restore state
109+
($node_master, $node_slave) = ($node_slave, $node_master);
110+
$node_slave->enable_streaming($node_master);
111+
$node_slave->append_conf('recovery.conf', qq(
112+
recovery_target_timeline='latest'
113+
));
114+
$node_slave->start;
115+
$node_slave->psql('postgres', "SELECT coalesce(sum(id),-1) FROM t_012_tbl",
116+
stdout => \$psql_out);
117+
is($psql_out, '8128', "Visible");
118+
119+
$node_master->psql('postgres', "DELETE FROM t_012_tbl");
120+
121+
# Function borrowed from src/test/regress/sql/hs_primary_extremes.sql
122+
$node_master->psql('postgres', "
123+
CREATE OR REPLACE FUNCTION hs_subxids (n integer)
124+
RETURNS void
125+
LANGUAGE plpgsql
126+
AS \$\$
127+
BEGIN
128+
IF n <= 0 THEN RETURN; END IF;
129+
INSERT INTO t_012_tbl VALUES (n);
130+
PERFORM hs_subxids(n - 1);
131+
RETURN;
132+
EXCEPTION WHEN raise_exception THEN NULL; END;
133+
\$\$;");
134+
$node_master->psql('postgres', "
135+
BEGIN;
136+
SELECT hs_subxids(127);
137+
PREPARE TRANSACTION 'xact_012_1';");
138+
$node_master->wait_for_catchup($node_slave, 'replay', $node_master->lsn('insert'));
139+
$node_slave->psql('postgres', "SELECT coalesce(sum(id),-1) FROM t_012_tbl",
140+
stdout => \$psql_out);
141+
is($psql_out, '-1', "Not visible");
142+
$node_master->stop;
143+
$node_slave->promote;
144+
$node_slave->poll_query_until('postgres',
145+
"SELECT NOT pg_is_in_recovery()")
146+
or die "Timed out while waiting for promotion of standby";
147+
148+
$node_slave->psql('postgres', "SELECT coalesce(sum(id),-1) FROM t_012_tbl",
149+
stdout => \$psql_out);
150+
is($psql_out, '-1', "Not visible");
151+
152+
# restore state
153+
($node_master, $node_slave) = ($node_slave, $node_master);
154+
$node_slave->enable_streaming($node_master);
155+
$node_slave->append_conf('recovery.conf', qq(
156+
recovery_target_timeline='latest'
157+
));
158+
$node_slave->start;
159+
$psql_rc = $node_master->psql('postgres', "COMMIT PREPARED 'xact_012_1'");
160+
is($psql_rc, '0', "Restore of PGPROC_MAX_CACHED_SUBXIDS+ prepared transaction on promoted slave");
161+
162+
$node_master->psql('postgres', "SELECT coalesce(sum(id),-1) FROM t_012_tbl",
163+
stdout => \$psql_out);
164+
is($psql_out, '8128', "Visible");
165+
166+
$node_master->psql('postgres', "DELETE FROM t_012_tbl");
167+
$node_master->psql('postgres', "
168+
BEGIN;
169+
SELECT hs_subxids(201);
170+
PREPARE TRANSACTION 'xact_012_1';");
171+
$node_master->wait_for_catchup($node_slave, 'replay', $node_master->lsn('insert'));
172+
$node_slave->psql('postgres', "SELECT coalesce(sum(id),-1) FROM t_012_tbl",
173+
stdout => \$psql_out);
174+
is($psql_out, '-1', "Not visible");
175+
$node_master->stop;
176+
$node_slave->promote;
177+
$node_slave->poll_query_until('postgres',
178+
"SELECT NOT pg_is_in_recovery()")
179+
or die "Timed out while waiting for promotion of standby";
180+
181+
$node_slave->psql('postgres', "SELECT coalesce(sum(id),-1) FROM t_012_tbl",
182+
stdout => \$psql_out);
183+
is($psql_out, '-1', "Not visible");
184+
185+
# restore state
186+
($node_master, $node_slave) = ($node_slave, $node_master);
187+
$node_slave->enable_streaming($node_master);
188+
$node_slave->append_conf('recovery.conf', qq(
189+
recovery_target_timeline='latest'
190+
));
191+
$node_slave->start;
192+
$psql_rc = $node_master->psql('postgres', "ROLLBACK PREPARED 'xact_012_1'");
193+
is($psql_rc, '0', "Rollback of PGPROC_MAX_CACHED_SUBXIDS+ prepared transaction on promoted slave");
194+
195+
$node_master->psql('postgres', "SELECT coalesce(sum(id),-1) FROM t_012_tbl",
196+
stdout => \$psql_out);
197+
is($psql_out, '-1', "Not visible");

0 commit comments

Comments
 (0)