Debian Codegraph @

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

raw
1
2 # Copyright (c) 2021-2025, PostgreSQL Global Development Group
3
4 # Tests dedicated to two-phase commit 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 my $psql_out = '';
13 my $psql_rc = '';
14
15 sub configure_and_reload
16 {
17 local $Test::Builder::Level = $Test::Builder::Level + 1;
18
19 my ($node, $parameter) = @_;
20 my $name = $node->name;
21
22 $node->append_conf(
23 'postgresql.conf', qq(
24 $parameter
25 ));
26 $node->psql('postgres', "SELECT pg_reload_conf()", stdout => \$psql_out);
27 is($psql_out, 't', "reload node $name with $parameter");
28 return;
29 }
30
31 # Set up two nodes, which will alternately be primary and replication standby.
32
33 # Setup london node
34 my $node_london = PostgreSQL::Test::Cluster->new("london");
35 $node_london->init(allows_streaming => 1);
36 $node_london->append_conf(
37 'postgresql.conf', qq(
38 max_prepared_transactions = 10
39 log_checkpoints = true
40 ));
41 $node_london->start;
42 $node_london->backup('london_backup');
43
44 # Setup paris node
45 my $node_paris = PostgreSQL::Test::Cluster->new('paris');
46 $node_paris->init_from_backup($node_london, 'london_backup',
47 has_streaming => 1);
48 $node_paris->append_conf(
49 'postgresql.conf', qq(
50 subtransaction_buffers = 32
51 ));
52 $node_paris->start;
53
54 # Switch to synchronous replication in both directions
55 configure_and_reload($node_london, "synchronous_standby_names = 'paris'");
56 configure_and_reload($node_paris, "synchronous_standby_names = 'london'");
57
58 # Set up nonce names for current primary and standby nodes
59 note "Initially, london is primary and paris is standby";
60 my ($cur_primary, $cur_standby) = ($node_london, $node_paris);
61 my $cur_primary_name = $cur_primary->name;
62
63 # Create table we'll use in the test transactions
64 $cur_primary->psql('postgres', "CREATE TABLE t_009_tbl (id int, msg text)");
65
66 ###############################################################################
67 # Check that we can commit and abort transaction after soft restart.
68 # Here checkpoint happens before shutdown and no WAL replay will occur at next
69 # startup. In this case postgres re-creates shared-memory state from twophase
70 # files.
71 ###############################################################################
72
73 $cur_primary->psql(
74 'postgres', "
75 BEGIN;
76 INSERT INTO t_009_tbl VALUES (1, 'issued to ${cur_primary_name}');
77 SAVEPOINT s1;
78 INSERT INTO t_009_tbl VALUES (2, 'issued to ${cur_primary_name}');
79 PREPARE TRANSACTION 'xact_009_1';
80 BEGIN;
81 INSERT INTO t_009_tbl VALUES (3, 'issued to ${cur_primary_name}');
82 SAVEPOINT s1;
83 INSERT INTO t_009_tbl VALUES (4, 'issued to ${cur_primary_name}');
84 PREPARE TRANSACTION 'xact_009_2';");
85 $cur_primary->stop;
86 $cur_primary->start;
87
88 $psql_rc = $cur_primary->psql('postgres', "COMMIT PREPARED 'xact_009_1'");
89 is($psql_rc, '0', 'Commit prepared transaction after restart');
90
91 $psql_rc = $cur_primary->psql('postgres', "ROLLBACK PREPARED 'xact_009_2'");
92 is($psql_rc, '0', 'Rollback prepared transaction after restart');
93
94 ###############################################################################
95 # Check that we can commit and abort after a hard restart.
96 # At next startup, WAL replay will re-create shared memory state for prepared
97 # transaction using dedicated WAL records.
98 ###############################################################################
99
100 $cur_primary->psql(
101 'postgres', "
102 CHECKPOINT;
103 BEGIN;
104 INSERT INTO t_009_tbl VALUES (5, 'issued to ${cur_primary_name}');
105 SAVEPOINT s1;
106 INSERT INTO t_009_tbl VALUES (6, 'issued to ${cur_primary_name}');
107 PREPARE TRANSACTION 'xact_009_3';
108 BEGIN;
109 INSERT INTO t_009_tbl VALUES (7, 'issued to ${cur_primary_name}');
110 SAVEPOINT s1;
111 INSERT INTO t_009_tbl VALUES (8, 'issued to ${cur_primary_name}');
112 PREPARE TRANSACTION 'xact_009_4';");
113 $cur_primary->teardown_node;
114 $cur_primary->start;
115
116 $psql_rc = $cur_primary->psql('postgres', "COMMIT PREPARED 'xact_009_3'");
117 is($psql_rc, '0', 'Commit prepared transaction after teardown');
118
119 $psql_rc = $cur_primary->psql('postgres', "ROLLBACK PREPARED 'xact_009_4'");
120 is($psql_rc, '0', 'Rollback prepared transaction after teardown');
121
122 ###############################################################################
123 # Check that WAL replay can handle several transactions with same GID name.
124 ###############################################################################
125
126 $cur_primary->psql(
127 'postgres', "
128 CHECKPOINT;
129 BEGIN;
130 INSERT INTO t_009_tbl VALUES (9, 'issued to ${cur_primary_name}');
131 SAVEPOINT s1;
132 INSERT INTO t_009_tbl VALUES (10, 'issued to ${cur_primary_name}');
133 PREPARE TRANSACTION 'xact_009_5';
134 COMMIT PREPARED 'xact_009_5';
135 BEGIN;
136 INSERT INTO t_009_tbl VALUES (11, 'issued to ${cur_primary_name}');
137 SAVEPOINT s1;
138 INSERT INTO t_009_tbl VALUES (12, 'issued to ${cur_primary_name}');
139 PREPARE TRANSACTION 'xact_009_5';");
140 $cur_primary->teardown_node;
141 $cur_primary->start;
142
143 $psql_rc = $cur_primary->psql('postgres', "COMMIT PREPARED 'xact_009_5'");
144 is($psql_rc, '0', 'Replay several transactions with same GID');
145
146 ###############################################################################
147 # Check that WAL replay cleans up its shared memory state and releases locks
148 # while replaying transaction commits.
149 ###############################################################################
150
151 $cur_primary->psql(
152 'postgres', "
153 BEGIN;
154 INSERT INTO t_009_tbl VALUES (13, 'issued to ${cur_primary_name}');
155 SAVEPOINT s1;
156 INSERT INTO t_009_tbl VALUES (14, 'issued to ${cur_primary_name}');
157 PREPARE TRANSACTION 'xact_009_6';
158 COMMIT PREPARED 'xact_009_6';");
159 $cur_primary->teardown_node;
160 $cur_primary->start;
161 $psql_rc = $cur_primary->psql(
162 'postgres', "
163 BEGIN;
164 INSERT INTO t_009_tbl VALUES (15, 'issued to ${cur_primary_name}');
165 SAVEPOINT s1;
166 INSERT INTO t_009_tbl VALUES (16, 'issued to ${cur_primary_name}');
167 -- This prepare can fail due to conflicting GID or locks conflicts if
168 -- replay did not fully cleanup its state on previous commit.
169 PREPARE TRANSACTION 'xact_009_7';");
170 is($psql_rc, '0', "Cleanup of shared memory state for 2PC commit");
171
172 $cur_primary->psql('postgres', "COMMIT PREPARED 'xact_009_7'");
173
174 ###############################################################################
175 # Check that WAL replay will cleanup its shared memory state on running standby.
176 ###############################################################################
177
178 $cur_primary->psql(
179 'postgres', "
180 BEGIN;
181 INSERT INTO t_009_tbl VALUES (17, 'issued to ${cur_primary_name}');
182 SAVEPOINT s1;
183 INSERT INTO t_009_tbl VALUES (18, 'issued to ${cur_primary_name}');
184 PREPARE TRANSACTION 'xact_009_8';
185 COMMIT PREPARED 'xact_009_8';");
186 $cur_standby->psql(
187 'postgres',
188 "SELECT count(*) FROM pg_prepared_xacts",
189 stdout => \$psql_out);
190 is($psql_out, '0',
191 "Cleanup of shared memory state on running standby without checkpoint");
192
193 ###############################################################################
194 # Same as in previous case, but let's force checkpoint on standby between
195 # prepare and commit to use on-disk twophase files.
196 ###############################################################################
197
198 $cur_primary->psql(
199 'postgres', "
200 BEGIN;
201 INSERT INTO t_009_tbl VALUES (19, 'issued to ${cur_primary_name}');
202 SAVEPOINT s1;
203 INSERT INTO t_009_tbl VALUES (20, 'issued to ${cur_primary_name}');
204 PREPARE TRANSACTION 'xact_009_9';");
205 $cur_standby->psql('postgres', "CHECKPOINT");
206 $cur_primary->psql('postgres', "COMMIT PREPARED 'xact_009_9'");
207 $cur_standby->psql(
208 'postgres',
209 "SELECT count(*) FROM pg_prepared_xacts",
210 stdout => \$psql_out);
211 is($psql_out, '0',
212 "Cleanup of shared memory state on running standby after checkpoint");
213
214 ###############################################################################
215 # Check that prepared transactions can be committed on promoted standby.
216 ###############################################################################
217
218 $cur_primary->psql(
219 'postgres', "
220 BEGIN;
221 INSERT INTO t_009_tbl VALUES (21, 'issued to ${cur_primary_name}');
222 SAVEPOINT s1;
223 INSERT INTO t_009_tbl VALUES (22, 'issued to ${cur_primary_name}');
224 PREPARE TRANSACTION 'xact_009_10';");
225 $cur_primary->stop;
226 $cur_standby->promote;
227
228 # change roles
229 note "Now paris is primary and london is standby";
230 ($cur_primary, $cur_standby) = ($node_paris, $node_london);
231 $cur_primary_name = $cur_primary->name;
232
233 # because london is not running at this point, we can't use syncrep commit
234 # on this command
235 $psql_rc = $cur_primary->psql('postgres',
236 "SET synchronous_commit = off; COMMIT PREPARED 'xact_009_10'");
237 is($psql_rc, '0', "Restore of prepared transaction on promoted standby");
238
239 # restart old primary as new standby
240 $cur_standby->enable_streaming($cur_primary);
241 $cur_standby->start;
242
243 ###############################################################################
244 # Check that prepared transactions are replayed after soft restart of standby
245 # while primary is down. Since standby knows that primary is down it uses a
246 # different code path on startup to ensure that the status of transactions is
247 # consistent.
248 ###############################################################################
249
250 $cur_primary->psql(
251 'postgres', "
252 BEGIN;
253 INSERT INTO t_009_tbl VALUES (23, 'issued to ${cur_primary_name}');
254 SAVEPOINT s1;
255 INSERT INTO t_009_tbl VALUES (24, 'issued to ${cur_primary_name}');
256 PREPARE TRANSACTION 'xact_009_11';");
257 $cur_primary->stop;
258 $cur_standby->restart;
259 $cur_standby->promote;
260
261 # change roles
262 note "Now london is primary and paris is standby";
263 ($cur_primary, $cur_standby) = ($node_london, $node_paris);
264 $cur_primary_name = $cur_primary->name;
265
266 $cur_primary->psql(
267 'postgres',
268 "SELECT count(*) FROM pg_prepared_xacts",
269 stdout => \$psql_out);
270 is($psql_out, '1',
271 "Restore prepared transactions from files with primary down");
272
273 # restart old primary as new standby
274 $cur_standby->enable_streaming($cur_primary);
275 $cur_standby->start;
276
277 $cur_primary->psql('postgres', "COMMIT PREPARED 'xact_009_11'");
278
279 ###############################################################################
280 # Check that prepared transactions are correctly replayed after standby hard
281 # restart while primary is down.
282 ###############################################################################
283
284 $cur_primary->psql(
285 'postgres', "
286 BEGIN;
287 INSERT INTO t_009_tbl VALUES (25, 'issued to ${cur_primary_name}');
288 SAVEPOINT s1;
289 INSERT INTO t_009_tbl VALUES (26, 'issued to ${cur_primary_name}');
290 PREPARE TRANSACTION 'xact_009_12';
291 ");
292 $cur_primary->stop;
293 $cur_standby->teardown_node;
294 $cur_standby->start;
295 $cur_standby->promote;
296
297 # change roles
298 note "Now paris is primary and london is standby";
299 ($cur_primary, $cur_standby) = ($node_paris, $node_london);
300 $cur_primary_name = $cur_primary->name;
301
302 $cur_primary->psql(
303 'postgres',
304 "SELECT count(*) FROM pg_prepared_xacts",
305 stdout => \$psql_out);
306 is($psql_out, '1',
307 "Restore prepared transactions from records with primary down");
308
309 # restart old primary as new standby
310 $cur_standby->enable_streaming($cur_primary);
311 $cur_standby->start;
312
313 $cur_primary->psql('postgres', "COMMIT PREPARED 'xact_009_12'");
314
315 ###############################################################################
316 # Check visibility of prepared transactions in standby after a restart while
317 # primary is down.
318 ###############################################################################
319
320 $cur_primary->psql(
321 'postgres', "
322 SET synchronous_commit='remote_apply'; -- To ensure the standby is caught up
323 CREATE TABLE t_009_tbl_standby_mvcc (id int, msg text);
324 BEGIN;
325 INSERT INTO t_009_tbl_standby_mvcc VALUES (1, 'issued to ${cur_primary_name}');
326 SAVEPOINT s1;
327 INSERT INTO t_009_tbl_standby_mvcc VALUES (2, 'issued to ${cur_primary_name}');
328 PREPARE TRANSACTION 'xact_009_standby_mvcc';
329 ");
330 $cur_primary->stop;
331 $cur_standby->restart;
332
333 # Acquire a snapshot in standby, before we commit the prepared transaction
334 my $standby_session =
335 $cur_standby->background_psql('postgres', on_error_die => 1);
336 $standby_session->query_safe("BEGIN ISOLATION LEVEL REPEATABLE READ");
337 $psql_out =
338 $standby_session->query_safe("SELECT count(*) FROM t_009_tbl_standby_mvcc");
339 is($psql_out, '0',
340 "Prepared transaction not visible in standby before commit");
341
342 # Commit the transaction in primary
343 $cur_primary->start;
344 $cur_primary->psql(
345 'postgres', "
346 SET synchronous_commit='remote_apply'; -- To ensure the standby is caught up
347 COMMIT PREPARED 'xact_009_standby_mvcc';
348 ");
349
350 # Still not visible to the old snapshot
351 $psql_out =
352 $standby_session->query_safe("SELECT count(*) FROM t_009_tbl_standby_mvcc");
353 is($psql_out, '0',
354 "Committed prepared transaction not visible to old snapshot in standby");
355
356 # Is visible to a new snapshot
357 $standby_session->query_safe("COMMIT");
358 $psql_out =
359 $standby_session->query_safe("SELECT count(*) FROM t_009_tbl_standby_mvcc");
360 is($psql_out, '2',
361 "Committed prepared transaction is visible to new snapshot in standby");
362 $standby_session->quit;
363
364 ###############################################################################
365 # Check for a lock conflict between prepared transaction with DDL inside and
366 # replay of XLOG_STANDBY_LOCK wal record.
367 ###############################################################################
368
369 $cur_primary->psql(
370 'postgres', "
371 BEGIN;
372 CREATE TABLE t_009_tbl2 (id int, msg text);
373 SAVEPOINT s1;
374 INSERT INTO t_009_tbl2 VALUES (27, 'issued to ${cur_primary_name}');
375 PREPARE TRANSACTION 'xact_009_13';
376 -- checkpoint will issue XLOG_STANDBY_LOCK that can conflict with lock
377 -- held by 'create table' statement
378 CHECKPOINT;
379 COMMIT PREPARED 'xact_009_13';");
380
381 # Ensure that last transaction is replayed on standby.
382 my $cur_primary_lsn =
383 $cur_primary->safe_psql('postgres', "SELECT pg_current_wal_lsn()");
384 my $caughtup_query =
385 "SELECT '$cur_primary_lsn'::pg_lsn <= pg_last_wal_replay_lsn()";
386 $cur_standby->poll_query_until('postgres', $caughtup_query)
387 or die "Timed out while waiting for standby to catch up";
388
389 $cur_standby->psql(
390 'postgres',
391 "SELECT count(*) FROM t_009_tbl2",
392 stdout => \$psql_out);
393 is($psql_out, '1', "Replay prepared transaction with DDL");
394
395 ###############################################################################
396 # Check recovery of prepared transaction with DDL inside after a hard restart
397 # of the primary.
398 ###############################################################################
399
400 $cur_primary->psql(
401 'postgres', "
402 BEGIN;
403 CREATE TABLE t_009_tbl3 (id int, msg text);
404 SAVEPOINT s1;
405 INSERT INTO t_009_tbl3 VALUES (28, 'issued to ${cur_primary_name}');
406 PREPARE TRANSACTION 'xact_009_14';
407 BEGIN;
408 CREATE TABLE t_009_tbl4 (id int, msg text);
409 SAVEPOINT s1;
410 INSERT INTO t_009_tbl4 VALUES (29, 'issued to ${cur_primary_name}');
411 PREPARE TRANSACTION 'xact_009_15';");
412
413 $cur_primary->teardown_node;
414 $cur_primary->start;
415
416 $psql_rc = $cur_primary->psql('postgres', "COMMIT PREPARED 'xact_009_14'");
417 is($psql_rc, '0', 'Commit prepared transaction after teardown');
418
419 $psql_rc = $cur_primary->psql('postgres', "ROLLBACK PREPARED 'xact_009_15'");
420 is($psql_rc, '0', 'Rollback prepared transaction after teardown');
421
422 ###############################################################################
423 # Check recovery of prepared transaction with DDL inside after a soft restart
424 # of the primary.
425 ###############################################################################
426
427 $cur_primary->psql(
428 'postgres', "
429 BEGIN;
430 CREATE TABLE t_009_tbl5 (id int, msg text);
431 SAVEPOINT s1;
432 INSERT INTO t_009_tbl5 VALUES (30, 'issued to ${cur_primary_name}');
433 PREPARE TRANSACTION 'xact_009_16';
434 BEGIN;
435 CREATE TABLE t_009_tbl6 (id int, msg text);
436 SAVEPOINT s1;
437 INSERT INTO t_009_tbl6 VALUES (31, 'issued to ${cur_primary_name}');
438 PREPARE TRANSACTION 'xact_009_17';");
439
440 $cur_primary->stop;
441 $cur_primary->start;
442
443 $psql_rc = $cur_primary->psql('postgres', "COMMIT PREPARED 'xact_009_16'");
444 is($psql_rc, '0', 'Commit prepared transaction after restart');
445
446 $psql_rc = $cur_primary->psql('postgres', "ROLLBACK PREPARED 'xact_009_17'");
447 is($psql_rc, '0', 'Rollback prepared transaction after restart');
448
449 ###############################################################################
450 # Verify expected data appears on both servers.
451 ###############################################################################
452
453 $cur_primary->psql(
454 'postgres',
455 "SELECT count(*) FROM pg_prepared_xacts",
456 stdout => \$psql_out);
457 is($psql_out, '0', "No uncommitted prepared transactions on primary");
458
459 $cur_primary->psql(
460 'postgres',
461 "SELECT * FROM t_009_tbl ORDER BY id",
462 stdout => \$psql_out);
463 is( $psql_out, qq{1|issued to london
464 2|issued to london
465 5|issued to london
466 6|issued to london
467 9|issued to london
468 10|issued to london
469 11|issued to london
470 12|issued to london
471 13|issued to london
472 14|issued to london
473 15|issued to london
474 16|issued to london
475 17|issued to london
476 18|issued to london
477 19|issued to london
478 20|issued to london
479 21|issued to london
480 22|issued to london
481 23|issued to paris
482 24|issued to paris
483 25|issued to london
484 26|issued to london},
485 "Check expected t_009_tbl data on primary");
486
487 $cur_primary->psql(
488 'postgres',
489 "SELECT * FROM t_009_tbl2",
490 stdout => \$psql_out);
491 is( $psql_out,
492 qq{27|issued to paris},
493 "Check expected t_009_tbl2 data on primary");
494
495 $cur_standby->psql(
496 'postgres',
497 "SELECT count(*) FROM pg_prepared_xacts",
498 stdout => \$psql_out);
499 is($psql_out, '0', "No uncommitted prepared transactions on standby");
500
501 $cur_standby->psql(
502 'postgres',
503 "SELECT * FROM t_009_tbl ORDER BY id",
504 stdout => \$psql_out);
505 is( $psql_out, qq{1|issued to london
506 2|issued to london
507 5|issued to london
508 6|issued to london
509 9|issued to london
510 10|issued to london
511 11|issued to london
512 12|issued to london
513 13|issued to london
514 14|issued to london
515 15|issued to london
516 16|issued to london
517 17|issued to london
518 18|issued to london
519 19|issued to london
520 20|issued to london
521 21|issued to london
522 22|issued to london
523 23|issued to paris
524 24|issued to paris
525 25|issued to london
526 26|issued to london},
527 "Check expected t_009_tbl data on standby");
528
529 $cur_standby->psql(
530 'postgres',
531 "SELECT * FROM t_009_tbl2",
532 stdout => \$psql_out);
533 is( $psql_out,
534 qq{27|issued to paris},
535 "Check expected t_009_tbl2 data on standby");
536
537
538 # Exercise the 2PC recovery code in StartupSUBTRANS, which is concerned with
539 # ensuring that enough pg_subtrans pages exist on disk to cover the range of
540 # prepared transactions at server start time. There's not much we can verify
541 # directly, but let's at least get the code to run.
542 $cur_standby->stop();
543 configure_and_reload($cur_primary, "synchronous_standby_names = ''");
544
545 $cur_primary->safe_psql('postgres', "CHECKPOINT");
546
547 my $start_lsn =
548 $cur_primary->safe_psql('postgres', 'select pg_current_wal_insert_lsn()');
549 $cur_primary->safe_psql('postgres',
550 "CREATE TABLE test(); BEGIN; CREATE TABLE test1(); PREPARE TRANSACTION 'foo';"
551 );
552 my $osubtrans = $cur_primary->safe_psql('postgres',
553 "select 'pg_subtrans/'||f, s.size from pg_ls_dir('pg_subtrans') f, pg_stat_file('pg_subtrans/'||f) s"
554 );
555 $cur_primary->pgbench(
556 '--no-vacuum --client=5 --transactions=1000',
557 0,
558 [],
559 [],
560 'pgbench run to cause pg_subtrans traffic',
561 {
562 '009_twophase.pgb' => 'insert into test default values'
563 });
564 # StartupSUBTRANS is exercised with a wide range of visible XIDs in this
565 # stop/start sequence, because we left a prepared transaction open above.
566 # Also, setting subtransaction_buffers to 32 above causes to switch SLRU
567 # bank, for additional code coverage.
568 $cur_primary->stop;
569 $cur_primary->start;
570 my $nsubtrans = $cur_primary->safe_psql('postgres',
571 "select 'pg_subtrans/'||f, s.size from pg_ls_dir('pg_subtrans') f, pg_stat_file('pg_subtrans/'||f) s"
572 );
573 isnt($osubtrans, $nsubtrans, "contents of pg_subtrans/ have changed");
574
575 done_testing();