1# Tests dedicated to subtransactions in recovery 2use strict; 3use warnings; 4 5use PostgresNode; 6use TestLib; 7use Test::More tests => 12; 8 9# Setup master node 10my $node_master = get_new_node("master"); 11$node_master->init(allows_streaming => 1); 12$node_master->append_conf( 13 'postgresql.conf', qq( 14 max_prepared_transactions = 10 15 log_checkpoints = true 16)); 17$node_master->start; 18$node_master->backup('master_backup'); 19$node_master->psql('postgres', "CREATE TABLE t_012_tbl (id int)"); 20 21# Setup standby node 22my $node_standby = get_new_node('standby'); 23$node_standby->init_from_backup($node_master, 'master_backup', 24 has_streaming => 1); 25$node_standby->start; 26 27# Switch to synchronous replication 28$node_master->append_conf( 29 'postgresql.conf', qq( 30 synchronous_standby_names = '*' 31)); 32$node_master->psql('postgres', "SELECT pg_reload_conf()"); 33 34my $psql_out = ''; 35my $psql_rc = ''; 36 37############################################################################### 38# Check that replay will correctly set SUBTRANS and properly advance nextXid 39# so that it won't conflict with savepoint xids. 40############################################################################### 41 42$node_master->psql( 43 'postgres', " 44 BEGIN; 45 DELETE FROM t_012_tbl; 46 INSERT INTO t_012_tbl VALUES (43); 47 SAVEPOINT s1; 48 INSERT INTO t_012_tbl VALUES (43); 49 SAVEPOINT s2; 50 INSERT INTO t_012_tbl VALUES (43); 51 SAVEPOINT s3; 52 INSERT INTO t_012_tbl VALUES (43); 53 SAVEPOINT s4; 54 INSERT INTO t_012_tbl VALUES (43); 55 SAVEPOINT s5; 56 INSERT INTO t_012_tbl VALUES (43); 57 PREPARE TRANSACTION 'xact_012_1'; 58 CHECKPOINT;"); 59 60$node_master->stop; 61$node_master->start; 62$node_master->psql( 63 'postgres', " 64 -- here we can get xid of previous savepoint if nextXid 65 -- wasn't properly advanced 66 BEGIN; 67 INSERT INTO t_012_tbl VALUES (142); 68 ROLLBACK; 69 COMMIT PREPARED 'xact_012_1';"); 70 71$node_master->psql( 72 'postgres', 73 "SELECT count(*) FROM t_012_tbl", 74 stdout => \$psql_out); 75is($psql_out, '6', "Check nextXid handling for prepared subtransactions"); 76 77############################################################################### 78# Check that replay will correctly set 2PC with more than 79# PGPROC_MAX_CACHED_SUBXIDS subtransations and also show data properly 80# on promotion 81############################################################################### 82$node_master->psql('postgres', "DELETE FROM t_012_tbl"); 83 84# Function borrowed from src/test/regress/sql/hs_primary_extremes.sql 85$node_master->psql( 86 'postgres', " 87 CREATE OR REPLACE FUNCTION hs_subxids (n integer) 88 RETURNS void 89 LANGUAGE plpgsql 90 AS \$\$ 91 BEGIN 92 IF n <= 0 THEN RETURN; END IF; 93 INSERT INTO t_012_tbl VALUES (n); 94 PERFORM hs_subxids(n - 1); 95 RETURN; 96 EXCEPTION WHEN raise_exception THEN NULL; END; 97 \$\$;"); 98$node_master->psql( 99 'postgres', " 100 BEGIN; 101 SELECT hs_subxids(127); 102 COMMIT;"); 103$node_master->wait_for_catchup($node_standby, 'replay', 104 $node_master->lsn('insert')); 105$node_standby->psql( 106 'postgres', 107 "SELECT coalesce(sum(id),-1) FROM t_012_tbl", 108 stdout => \$psql_out); 109is($psql_out, '8128', "Visible"); 110$node_master->stop; 111$node_standby->promote; 112 113$node_standby->psql( 114 'postgres', 115 "SELECT coalesce(sum(id),-1) FROM t_012_tbl", 116 stdout => \$psql_out); 117is($psql_out, '8128', "Visible"); 118 119# restore state 120($node_master, $node_standby) = ($node_standby, $node_master); 121$node_standby->enable_streaming($node_master); 122$node_standby->append_conf( 123 'recovery.conf', qq( 124recovery_target_timeline='latest' 125)); 126$node_standby->start; 127$node_standby->psql( 128 'postgres', 129 "SELECT coalesce(sum(id),-1) FROM t_012_tbl", 130 stdout => \$psql_out); 131is($psql_out, '8128', "Visible"); 132 133$node_master->psql('postgres', "DELETE FROM t_012_tbl"); 134 135# Function borrowed from src/test/regress/sql/hs_primary_extremes.sql 136$node_master->psql( 137 'postgres', " 138 CREATE OR REPLACE FUNCTION hs_subxids (n integer) 139 RETURNS void 140 LANGUAGE plpgsql 141 AS \$\$ 142 BEGIN 143 IF n <= 0 THEN RETURN; END IF; 144 INSERT INTO t_012_tbl VALUES (n); 145 PERFORM hs_subxids(n - 1); 146 RETURN; 147 EXCEPTION WHEN raise_exception THEN NULL; END; 148 \$\$;"); 149$node_master->psql( 150 'postgres', " 151 BEGIN; 152 SELECT hs_subxids(127); 153 PREPARE TRANSACTION 'xact_012_1';"); 154$node_master->wait_for_catchup($node_standby, 'replay', 155 $node_master->lsn('insert')); 156$node_standby->psql( 157 'postgres', 158 "SELECT coalesce(sum(id),-1) FROM t_012_tbl", 159 stdout => \$psql_out); 160is($psql_out, '-1', "Not visible"); 161$node_master->stop; 162$node_standby->promote; 163 164$node_standby->psql( 165 'postgres', 166 "SELECT coalesce(sum(id),-1) FROM t_012_tbl", 167 stdout => \$psql_out); 168is($psql_out, '-1', "Not visible"); 169 170# restore state 171($node_master, $node_standby) = ($node_standby, $node_master); 172$node_standby->enable_streaming($node_master); 173$node_standby->append_conf( 174 'recovery.conf', qq( 175recovery_target_timeline='latest' 176)); 177$node_standby->start; 178$psql_rc = $node_master->psql('postgres', "COMMIT PREPARED 'xact_012_1'"); 179is($psql_rc, '0', 180"Restore of PGPROC_MAX_CACHED_SUBXIDS+ prepared transaction on promoted standby" 181); 182 183$node_master->psql( 184 'postgres', 185 "SELECT coalesce(sum(id),-1) FROM t_012_tbl", 186 stdout => \$psql_out); 187is($psql_out, '8128', "Visible"); 188 189$node_master->psql('postgres', "DELETE FROM t_012_tbl"); 190$node_master->psql( 191 'postgres', " 192 BEGIN; 193 SELECT hs_subxids(201); 194 PREPARE TRANSACTION 'xact_012_1';"); 195$node_master->wait_for_catchup($node_standby, 'replay', 196 $node_master->lsn('insert')); 197$node_standby->psql( 198 'postgres', 199 "SELECT coalesce(sum(id),-1) FROM t_012_tbl", 200 stdout => \$psql_out); 201is($psql_out, '-1', "Not visible"); 202$node_master->stop; 203$node_standby->promote; 204 205$node_standby->psql( 206 'postgres', 207 "SELECT coalesce(sum(id),-1) FROM t_012_tbl", 208 stdout => \$psql_out); 209is($psql_out, '-1', "Not visible"); 210 211# restore state 212($node_master, $node_standby) = ($node_standby, $node_master); 213$node_standby->enable_streaming($node_master); 214$node_standby->append_conf( 215 'recovery.conf', qq( 216recovery_target_timeline='latest' 217)); 218$node_standby->start; 219$psql_rc = $node_master->psql('postgres', "ROLLBACK PREPARED 'xact_012_1'"); 220is($psql_rc, '0', 221"Rollback of PGPROC_MAX_CACHED_SUBXIDS+ prepared transaction on promoted standby" 222); 223 224$node_master->psql( 225 'postgres', 226 "SELECT coalesce(sum(id),-1) FROM t_012_tbl", 227 stdout => \$psql_out); 228is($psql_out, '-1', "Not visible"); 229