1 2# Copyright (c) 2021, PostgreSQL Global Development Group 3 4# Test CREATE INDEX CONCURRENTLY with concurrent prepared-xact modifications 5use strict; 6use warnings; 7 8use Config; 9use PostgresNode; 10use TestLib; 11 12use Test::More tests => 5; 13 14my ($node, $result); 15 16# 17# Test set-up 18# 19$node = get_new_node('CIC_2PC_test'); 20$node->init; 21$node->append_conf('postgresql.conf', 'max_prepared_transactions = 10'); 22$node->append_conf('postgresql.conf', 'lock_timeout = 180000'); 23$node->start; 24$node->safe_psql('postgres', q(CREATE EXTENSION amcheck)); 25$node->safe_psql('postgres', q(CREATE TABLE tbl(i int))); 26 27 28# 29# Run 3 overlapping 2PC transactions with CIC 30# 31# We have two concurrent background psql processes: $main_h for INSERTs and 32# $cic_h for CIC. Also, we use non-background psql for some COMMIT PREPARED 33# statements. 34# 35 36my $main_in = ''; 37my $main_out = ''; 38my $main_timer = IPC::Run::timeout(180); 39 40my $main_h = 41 $node->background_psql('postgres', \$main_in, \$main_out, 42 $main_timer, on_error_stop => 1); 43$main_in .= q( 44BEGIN; 45INSERT INTO tbl VALUES(0); 46\echo syncpoint1 47); 48pump $main_h until $main_out =~ /syncpoint1/ || $main_timer->is_expired; 49 50my $cic_in = ''; 51my $cic_out = ''; 52my $cic_timer = IPC::Run::timeout(180); 53my $cic_h = 54 $node->background_psql('postgres', \$cic_in, \$cic_out, 55 $cic_timer, on_error_stop => 1); 56$cic_in .= q( 57\echo start 58CREATE INDEX CONCURRENTLY idx ON tbl(i); 59); 60pump $cic_h until $cic_out =~ /start/ || $cic_timer->is_expired; 61 62$main_in .= q( 63PREPARE TRANSACTION 'a'; 64); 65 66$main_in .= q( 67BEGIN; 68INSERT INTO tbl VALUES(0); 69\echo syncpoint2 70); 71pump $main_h until $main_out =~ /syncpoint2/ || $main_timer->is_expired; 72 73$node->safe_psql('postgres', q(COMMIT PREPARED 'a';)); 74 75$main_in .= q( 76PREPARE TRANSACTION 'b'; 77BEGIN; 78INSERT INTO tbl VALUES(0); 79\echo syncpoint3 80); 81pump $main_h until $main_out =~ /syncpoint3/ || $main_timer->is_expired; 82 83$node->safe_psql('postgres', q(COMMIT PREPARED 'b';)); 84 85$main_in .= q( 86PREPARE TRANSACTION 'c'; 87COMMIT PREPARED 'c'; 88); 89$main_h->pump_nb; 90 91$main_h->finish; 92$cic_h->finish; 93 94$result = $node->psql('postgres', q(SELECT bt_index_check('idx',true))); 95is($result, '0', 'bt_index_check after overlapping 2PC'); 96 97 98# 99# Server restart shall not change whether prepared xact blocks CIC 100# 101 102$node->safe_psql( 103 'postgres', q( 104BEGIN; 105INSERT INTO tbl VALUES(0); 106PREPARE TRANSACTION 'spans_restart'; 107BEGIN; 108CREATE TABLE unused (); 109PREPARE TRANSACTION 'persists_forever'; 110)); 111$node->restart; 112 113my $reindex_in = ''; 114my $reindex_out = ''; 115my $reindex_timer = IPC::Run::timeout(180); 116my $reindex_h = 117 $node->background_psql('postgres', \$reindex_in, \$reindex_out, 118 $reindex_timer, on_error_stop => 1); 119$reindex_in .= q( 120\echo start 121DROP INDEX CONCURRENTLY idx; 122CREATE INDEX CONCURRENTLY idx ON tbl(i); 123); 124pump $reindex_h until $reindex_out =~ /start/ || $reindex_timer->is_expired; 125 126$node->safe_psql('postgres', "COMMIT PREPARED 'spans_restart'"); 127$reindex_h->finish; 128$result = $node->psql('postgres', q(SELECT bt_index_check('idx',true))); 129is($result, '0', 'bt_index_check after 2PC and restart'); 130 131 132# 133# Stress CIC+2PC with pgbench 134# 135# pgbench might try to launch more than one instance of the CIC 136# transaction concurrently. That would deadlock, so use an advisory 137# lock to ensure only one CIC runs at a time. 138 139# Fix broken index first 140$node->safe_psql('postgres', q(REINDEX TABLE tbl;)); 141 142# Run pgbench. 143$node->pgbench( 144 '--no-vacuum --client=5 --transactions=100', 145 0, 146 [qr{actually processed}], 147 [qr{^$}], 148 'concurrent INSERTs w/ 2PC and CIC', 149 { 150 '003_pgbench_concurrent_2pc' => q( 151 BEGIN; 152 INSERT INTO tbl VALUES(0); 153 PREPARE TRANSACTION 'c:client_id'; 154 COMMIT PREPARED 'c:client_id'; 155 ), 156 '003_pgbench_concurrent_2pc_savepoint' => q( 157 BEGIN; 158 SAVEPOINT s1; 159 INSERT INTO tbl VALUES(0); 160 PREPARE TRANSACTION 'c:client_id'; 161 COMMIT PREPARED 'c:client_id'; 162 ), 163 '003_pgbench_concurrent_cic' => q( 164 SELECT pg_try_advisory_lock(42)::integer AS gotlock \gset 165 \if :gotlock 166 DROP INDEX CONCURRENTLY idx; 167 CREATE INDEX CONCURRENTLY idx ON tbl(i); 168 SELECT bt_index_check('idx',true); 169 SELECT pg_advisory_unlock(42); 170 \endif 171 ), 172 '004_pgbench_concurrent_ric' => q( 173 SELECT pg_try_advisory_lock(42)::integer AS gotlock \gset 174 \if :gotlock 175 REINDEX INDEX CONCURRENTLY idx; 176 SELECT bt_index_check('idx',true); 177 SELECT pg_advisory_unlock(42); 178 \endif 179 ) 180 }); 181 182$node->stop; 183done_testing(); 184