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