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