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

   1
   2 # Copyright (c) 2021-2022, PostgreSQL Global Development Group
   3
   4 # Tests dedicated to two-phase commit in recovery
   5 use strict;
   6 use warnings;
   7
   8 use PostgreSQL::Test::Cluster;
   9 use PostgreSQL::Test::Utils;
  10 use Test::More tests => 24;
  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->start;
  49
  50 # Switch to synchronous replication in both directions
  51 configure_and_reload($node_london, "synchronous_standby_names = 'paris'");
  52 configure_and_reload($node_paris,  "synchronous_standby_names = 'london'");
  53
  54 # Set up nonce names for current primary and standby nodes
  55 note "Initially, london is primary and paris is standby";
  56 my ($cur_primary, $cur_standby) = ($node_london, $node_paris);
  57 my $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'");
  85 is($psql_rc, '0', 'Commit prepared transaction after restart');
  86
  87 $psql_rc = $cur_primary->psql('postgres', "ROLLBACK PREPARED 'xact_009_2'");
  88 is($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'");
 113 is($psql_rc, '0', 'Commit prepared transaction after teardown');
 114
 115 $psql_rc = $cur_primary->psql('postgres', "ROLLBACK PREPARED 'xact_009_4'");
 116 is($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'");
 140 is($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';");
 166 is($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);
 186 is($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);
 207 is($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
 225 note "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'");
 233 is($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
 258 note "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);
 266 is($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
 294 note "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);
 302 is($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.
 329 my $cur_primary_lsn =
 330   $cur_primary->safe_psql('postgres', "SELECT pg_current_wal_lsn()");
 331 my $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);
 340 is($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'");
 364 is($psql_rc, '0', 'Commit prepared transaction after teardown');
 365
 366 $psql_rc = $cur_primary->psql('postgres', "ROLLBACK PREPARED 'xact_009_15'");
 367 is($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'");
 391 is($psql_rc, '0', 'Commit prepared transaction after restart');
 392
 393 $psql_rc = $cur_primary->psql('postgres', "ROLLBACK PREPARED 'xact_009_17'");
 394 is($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);
 404 is($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);
 410 is( $psql_out, qq{1|issued to london
 411 2|issued to london
 412 5|issued to london
 413 6|issued to london
 414 9|issued to london
 415 10|issued to london
 416 11|issued to london
 417 12|issued to london
 418 13|issued to london
 419 14|issued to london
 420 15|issued to london
 421 16|issued to london
 422 17|issued to london
 423 18|issued to london
 424 19|issued to london
 425 20|issued to london
 426 21|issued to london
 427 22|issued to london
 428 23|issued to paris
 429 24|issued to paris
 430 25|issued to london
 431 26|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);
 438 is( $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);
 446 is($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);
 452 is( $psql_out, qq{1|issued to london
 453 2|issued to london
 454 5|issued to london
 455 6|issued to london
 456 9|issued to london
 457 10|issued to london
 458 11|issued to london
 459 12|issued to london
 460 13|issued to london
 461 14|issued to london
 462 15|issued to london
 463 16|issued to london
 464 17|issued to london
 465 18|issued to london
 466 19|issued to london
 467 20|issued to london
 468 21|issued to london
 469 22|issued to london
 470 23|issued to paris
 471 24|issued to paris
 472 25|issued to london
 473 26|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);
 480 is( $psql_out,
 481         qq{27|issued to paris},
 482         "Check expected t_009_tbl2 data on standby");