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