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