Debian Codegraph @

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

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