1 2# Copyright (c) 2021, PostgreSQL Global Development Group 3 4# Tests dedicated to two-phase commit in recovery 5use strict; 6use warnings; 7 8use PostgresNode; 9use TestLib; 10use Test::More tests => 24; 11 12my $psql_out = ''; 13my $psql_rc = ''; 14 15sub 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 34my $node_london = get_new_node("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 45my $node_paris = get_new_node('paris'); 46$node_paris->init_from_backup($node_london, 'london_backup', 47 has_streaming => 1); 48$node_paris->start; 49 50# Switch to synchronous replication in both directions 51configure_and_reload($node_london, "synchronous_standby_names = 'paris'"); 52configure_and_reload($node_paris, "synchronous_standby_names = 'london'"); 53 54# Set up nonce names for current primary and standby nodes 55note "Initially, london is primary and paris is standby"; 56my ($cur_primary, $cur_standby) = ($node_london, $node_paris); 57my $cur_primary_name = $cur_primary->name; 58 59# Create table we'll use in the test transactions 60$cur_primary->psql('postgres', "CREATE TABLE t_009_tbl (id int, msg text)"); 61 62############################################################################### 63# Check that we can commit and abort transaction after soft restart. 64# Here checkpoint happens before shutdown and no WAL replay will occur at next 65# startup. In this case postgres re-creates shared-memory state from twophase 66# files. 67############################################################################### 68 69$cur_primary->psql( 70 'postgres', " 71 BEGIN; 72 INSERT INTO t_009_tbl VALUES (1, 'issued to ${cur_primary_name}'); 73 SAVEPOINT s1; 74 INSERT INTO t_009_tbl VALUES (2, 'issued to ${cur_primary_name}'); 75 PREPARE TRANSACTION 'xact_009_1'; 76 BEGIN; 77 INSERT INTO t_009_tbl VALUES (3, 'issued to ${cur_primary_name}'); 78 SAVEPOINT s1; 79 INSERT INTO t_009_tbl VALUES (4, 'issued to ${cur_primary_name}'); 80 PREPARE TRANSACTION 'xact_009_2';"); 81$cur_primary->stop; 82$cur_primary->start; 83 84$psql_rc = $cur_primary->psql('postgres', "COMMIT PREPARED 'xact_009_1'"); 85is($psql_rc, '0', 'Commit prepared transaction after restart'); 86 87$psql_rc = $cur_primary->psql('postgres', "ROLLBACK PREPARED 'xact_009_2'"); 88is($psql_rc, '0', 'Rollback prepared transaction after restart'); 89 90############################################################################### 91# Check that we can commit and abort after a hard restart. 92# At next startup, WAL replay will re-create shared memory state for prepared 93# transaction using dedicated WAL records. 94############################################################################### 95 96$cur_primary->psql( 97 'postgres', " 98 CHECKPOINT; 99 BEGIN; 100 INSERT INTO t_009_tbl VALUES (5, 'issued to ${cur_primary_name}'); 101 SAVEPOINT s1; 102 INSERT INTO t_009_tbl VALUES (6, 'issued to ${cur_primary_name}'); 103 PREPARE TRANSACTION 'xact_009_3'; 104 BEGIN; 105 INSERT INTO t_009_tbl VALUES (7, 'issued to ${cur_primary_name}'); 106 SAVEPOINT s1; 107 INSERT INTO t_009_tbl VALUES (8, 'issued to ${cur_primary_name}'); 108 PREPARE TRANSACTION 'xact_009_4';"); 109$cur_primary->teardown_node; 110$cur_primary->start; 111 112$psql_rc = $cur_primary->psql('postgres', "COMMIT PREPARED 'xact_009_3'"); 113is($psql_rc, '0', 'Commit prepared transaction after teardown'); 114 115$psql_rc = $cur_primary->psql('postgres', "ROLLBACK PREPARED 'xact_009_4'"); 116is($psql_rc, '0', 'Rollback prepared transaction after teardown'); 117 118############################################################################### 119# Check that WAL replay can handle several transactions with same GID name. 120############################################################################### 121 122$cur_primary->psql( 123 'postgres', " 124 CHECKPOINT; 125 BEGIN; 126 INSERT INTO t_009_tbl VALUES (9, 'issued to ${cur_primary_name}'); 127 SAVEPOINT s1; 128 INSERT INTO t_009_tbl VALUES (10, 'issued to ${cur_primary_name}'); 129 PREPARE TRANSACTION 'xact_009_5'; 130 COMMIT PREPARED 'xact_009_5'; 131 BEGIN; 132 INSERT INTO t_009_tbl VALUES (11, 'issued to ${cur_primary_name}'); 133 SAVEPOINT s1; 134 INSERT INTO t_009_tbl VALUES (12, 'issued to ${cur_primary_name}'); 135 PREPARE TRANSACTION 'xact_009_5';"); 136$cur_primary->teardown_node; 137$cur_primary->start; 138 139$psql_rc = $cur_primary->psql('postgres', "COMMIT PREPARED 'xact_009_5'"); 140is($psql_rc, '0', 'Replay several transactions with same GID'); 141 142############################################################################### 143# Check that WAL replay cleans up its shared memory state and releases locks 144# while replaying transaction commits. 145############################################################################### 146 147$cur_primary->psql( 148 'postgres', " 149 BEGIN; 150 INSERT INTO t_009_tbl VALUES (13, 'issued to ${cur_primary_name}'); 151 SAVEPOINT s1; 152 INSERT INTO t_009_tbl VALUES (14, 'issued to ${cur_primary_name}'); 153 PREPARE TRANSACTION 'xact_009_6'; 154 COMMIT PREPARED 'xact_009_6';"); 155$cur_primary->teardown_node; 156$cur_primary->start; 157$psql_rc = $cur_primary->psql( 158 'postgres', " 159 BEGIN; 160 INSERT INTO t_009_tbl VALUES (15, 'issued to ${cur_primary_name}'); 161 SAVEPOINT s1; 162 INSERT INTO t_009_tbl VALUES (16, 'issued to ${cur_primary_name}'); 163 -- This prepare can fail due to conflicting GID or locks conflicts if 164 -- replay did not fully cleanup its state on previous commit. 165 PREPARE TRANSACTION 'xact_009_7';"); 166is($psql_rc, '0', "Cleanup of shared memory state for 2PC commit"); 167 168$cur_primary->psql('postgres', "COMMIT PREPARED 'xact_009_7'"); 169 170############################################################################### 171# Check that WAL replay will cleanup its shared memory state on running standby. 172############################################################################### 173 174$cur_primary->psql( 175 'postgres', " 176 BEGIN; 177 INSERT INTO t_009_tbl VALUES (17, 'issued to ${cur_primary_name}'); 178 SAVEPOINT s1; 179 INSERT INTO t_009_tbl VALUES (18, 'issued to ${cur_primary_name}'); 180 PREPARE TRANSACTION 'xact_009_8'; 181 COMMIT PREPARED 'xact_009_8';"); 182$cur_standby->psql( 183 'postgres', 184 "SELECT count(*) FROM pg_prepared_xacts", 185 stdout => \$psql_out); 186is($psql_out, '0', 187 "Cleanup of shared memory state on running standby without checkpoint"); 188 189############################################################################### 190# Same as in previous case, but let's force checkpoint on standby between 191# prepare and commit to use on-disk twophase files. 192############################################################################### 193 194$cur_primary->psql( 195 'postgres', " 196 BEGIN; 197 INSERT INTO t_009_tbl VALUES (19, 'issued to ${cur_primary_name}'); 198 SAVEPOINT s1; 199 INSERT INTO t_009_tbl VALUES (20, 'issued to ${cur_primary_name}'); 200 PREPARE TRANSACTION 'xact_009_9';"); 201$cur_standby->psql('postgres', "CHECKPOINT"); 202$cur_primary->psql('postgres', "COMMIT PREPARED 'xact_009_9'"); 203$cur_standby->psql( 204 'postgres', 205 "SELECT count(*) FROM pg_prepared_xacts", 206 stdout => \$psql_out); 207is($psql_out, '0', 208 "Cleanup of shared memory state on running standby after checkpoint"); 209 210############################################################################### 211# Check that prepared transactions can be committed on promoted standby. 212############################################################################### 213 214$cur_primary->psql( 215 'postgres', " 216 BEGIN; 217 INSERT INTO t_009_tbl VALUES (21, 'issued to ${cur_primary_name}'); 218 SAVEPOINT s1; 219 INSERT INTO t_009_tbl VALUES (22, 'issued to ${cur_primary_name}'); 220 PREPARE TRANSACTION 'xact_009_10';"); 221$cur_primary->teardown_node; 222$cur_standby->promote; 223 224# change roles 225note "Now paris is primary and london is standby"; 226($cur_primary, $cur_standby) = ($node_paris, $node_london); 227$cur_primary_name = $cur_primary->name; 228 229# because london is not running at this point, we can't use syncrep commit 230# on this command 231$psql_rc = $cur_primary->psql('postgres', 232 "SET synchronous_commit = off; COMMIT PREPARED 'xact_009_10'"); 233is($psql_rc, '0', "Restore of prepared transaction on promoted standby"); 234 235# restart old primary as new standby 236$cur_standby->enable_streaming($cur_primary); 237$cur_standby->start; 238 239############################################################################### 240# Check that prepared transactions are replayed after soft restart of standby 241# while primary is down. Since standby knows that primary is down it uses a 242# different code path on startup to ensure that the status of transactions is 243# consistent. 244############################################################################### 245 246$cur_primary->psql( 247 'postgres', " 248 BEGIN; 249 INSERT INTO t_009_tbl VALUES (23, 'issued to ${cur_primary_name}'); 250 SAVEPOINT s1; 251 INSERT INTO t_009_tbl VALUES (24, 'issued to ${cur_primary_name}'); 252 PREPARE TRANSACTION 'xact_009_11';"); 253$cur_primary->stop; 254$cur_standby->restart; 255$cur_standby->promote; 256 257# change roles 258note "Now london is primary and paris is standby"; 259($cur_primary, $cur_standby) = ($node_london, $node_paris); 260$cur_primary_name = $cur_primary->name; 261 262$cur_primary->psql( 263 'postgres', 264 "SELECT count(*) FROM pg_prepared_xacts", 265 stdout => \$psql_out); 266is($psql_out, '1', 267 "Restore prepared transactions from files with primary down"); 268 269# restart old primary as new standby 270$cur_standby->enable_streaming($cur_primary); 271$cur_standby->start; 272 273$cur_primary->psql('postgres', "COMMIT PREPARED 'xact_009_11'"); 274 275############################################################################### 276# Check that prepared transactions are correctly replayed after standby hard 277# restart while primary is down. 278############################################################################### 279 280$cur_primary->psql( 281 'postgres', " 282 BEGIN; 283 INSERT INTO t_009_tbl VALUES (25, 'issued to ${cur_primary_name}'); 284 SAVEPOINT s1; 285 INSERT INTO t_009_tbl VALUES (26, 'issued to ${cur_primary_name}'); 286 PREPARE TRANSACTION 'xact_009_12'; 287 "); 288$cur_primary->stop; 289$cur_standby->teardown_node; 290$cur_standby->start; 291$cur_standby->promote; 292 293# change roles 294note "Now paris is primary and london is standby"; 295($cur_primary, $cur_standby) = ($node_paris, $node_london); 296$cur_primary_name = $cur_primary->name; 297 298$cur_primary->psql( 299 'postgres', 300 "SELECT count(*) FROM pg_prepared_xacts", 301 stdout => \$psql_out); 302is($psql_out, '1', 303 "Restore prepared transactions from records with primary down"); 304 305# restart old primary as new standby 306$cur_standby->enable_streaming($cur_primary); 307$cur_standby->start; 308 309$cur_primary->psql('postgres', "COMMIT PREPARED 'xact_009_12'"); 310 311############################################################################### 312# Check for a lock conflict between prepared transaction with DDL inside and 313# replay of XLOG_STANDBY_LOCK wal record. 314############################################################################### 315 316$cur_primary->psql( 317 'postgres', " 318 BEGIN; 319 CREATE TABLE t_009_tbl2 (id int, msg text); 320 SAVEPOINT s1; 321 INSERT INTO t_009_tbl2 VALUES (27, 'issued to ${cur_primary_name}'); 322 PREPARE TRANSACTION 'xact_009_13'; 323 -- checkpoint will issue XLOG_STANDBY_LOCK that can conflict with lock 324 -- held by 'create table' statement 325 CHECKPOINT; 326 COMMIT PREPARED 'xact_009_13';"); 327 328# Ensure that last transaction is replayed on standby. 329my $cur_primary_lsn = 330 $cur_primary->safe_psql('postgres', "SELECT pg_current_wal_lsn()"); 331my $caughtup_query = 332 "SELECT '$cur_primary_lsn'::pg_lsn <= pg_last_wal_replay_lsn()"; 333$cur_standby->poll_query_until('postgres', $caughtup_query) 334 or die "Timed out while waiting for standby to catch up"; 335 336$cur_standby->psql( 337 'postgres', 338 "SELECT count(*) FROM t_009_tbl2", 339 stdout => \$psql_out); 340is($psql_out, '1', "Replay prepared transaction with DDL"); 341 342############################################################################### 343# Check recovery of prepared transaction with DDL inside after a hard restart 344# of the primary. 345############################################################################### 346 347$cur_primary->psql( 348 'postgres', " 349 BEGIN; 350 CREATE TABLE t_009_tbl3 (id int, msg text); 351 SAVEPOINT s1; 352 INSERT INTO t_009_tbl3 VALUES (28, 'issued to ${cur_primary_name}'); 353 PREPARE TRANSACTION 'xact_009_14'; 354 BEGIN; 355 CREATE TABLE t_009_tbl4 (id int, msg text); 356 SAVEPOINT s1; 357 INSERT INTO t_009_tbl4 VALUES (29, 'issued to ${cur_primary_name}'); 358 PREPARE TRANSACTION 'xact_009_15';"); 359 360$cur_primary->teardown_node; 361$cur_primary->start; 362 363$psql_rc = $cur_primary->psql('postgres', "COMMIT PREPARED 'xact_009_14'"); 364is($psql_rc, '0', 'Commit prepared transaction after teardown'); 365 366$psql_rc = $cur_primary->psql('postgres', "ROLLBACK PREPARED 'xact_009_15'"); 367is($psql_rc, '0', 'Rollback prepared transaction after teardown'); 368 369############################################################################### 370# Check recovery of prepared transaction with DDL inside after a soft restart 371# of the primary. 372############################################################################### 373 374$cur_primary->psql( 375 'postgres', " 376 BEGIN; 377 CREATE TABLE t_009_tbl5 (id int, msg text); 378 SAVEPOINT s1; 379 INSERT INTO t_009_tbl5 VALUES (30, 'issued to ${cur_primary_name}'); 380 PREPARE TRANSACTION 'xact_009_16'; 381 BEGIN; 382 CREATE TABLE t_009_tbl6 (id int, msg text); 383 SAVEPOINT s1; 384 INSERT INTO t_009_tbl6 VALUES (31, 'issued to ${cur_primary_name}'); 385 PREPARE TRANSACTION 'xact_009_17';"); 386 387$cur_primary->stop; 388$cur_primary->start; 389 390$psql_rc = $cur_primary->psql('postgres', "COMMIT PREPARED 'xact_009_16'"); 391is($psql_rc, '0', 'Commit prepared transaction after restart'); 392 393$psql_rc = $cur_primary->psql('postgres', "ROLLBACK PREPARED 'xact_009_17'"); 394is($psql_rc, '0', 'Rollback prepared transaction after restart'); 395 396############################################################################### 397# Verify expected data appears on both servers. 398############################################################################### 399 400$cur_primary->psql( 401 'postgres', 402 "SELECT count(*) FROM pg_prepared_xacts", 403 stdout => \$psql_out); 404is($psql_out, '0', "No uncommitted prepared transactions on primary"); 405 406$cur_primary->psql( 407 'postgres', 408 "SELECT * FROM t_009_tbl ORDER BY id", 409 stdout => \$psql_out); 410is( $psql_out, qq{1|issued to london 4112|issued to london 4125|issued to london 4136|issued to london 4149|issued to london 41510|issued to london 41611|issued to london 41712|issued to london 41813|issued to london 41914|issued to london 42015|issued to london 42116|issued to london 42217|issued to london 42318|issued to london 42419|issued to london 42520|issued to london 42621|issued to london 42722|issued to london 42823|issued to paris 42924|issued to paris 43025|issued to london 43126|issued to london}, 432 "Check expected t_009_tbl data on primary"); 433 434$cur_primary->psql( 435 'postgres', 436 "SELECT * FROM t_009_tbl2", 437 stdout => \$psql_out); 438is( $psql_out, 439 qq{27|issued to paris}, 440 "Check expected t_009_tbl2 data on primary"); 441 442$cur_standby->psql( 443 'postgres', 444 "SELECT count(*) FROM pg_prepared_xacts", 445 stdout => \$psql_out); 446is($psql_out, '0', "No uncommitted prepared transactions on standby"); 447 448$cur_standby->psql( 449 'postgres', 450 "SELECT * FROM t_009_tbl ORDER BY id", 451 stdout => \$psql_out); 452is( $psql_out, qq{1|issued to london 4532|issued to london 4545|issued to london 4556|issued to london 4569|issued to london 45710|issued to london 45811|issued to london 45912|issued to london 46013|issued to london 46114|issued to london 46215|issued to london 46316|issued to london 46417|issued to london 46518|issued to london 46619|issued to london 46720|issued to london 46821|issued to london 46922|issued to london 47023|issued to paris 47124|issued to paris 47225|issued to london 47326|issued to london}, 474 "Check expected t_009_tbl data on standby"); 475 476$cur_standby->psql( 477 'postgres', 478 "SELECT * FROM t_009_tbl2", 479 stdout => \$psql_out); 480is( $psql_out, 481 qq{27|issued to paris}, 482 "Check expected t_009_tbl2 data on standby"); 483