Debian Codegraph @

src/test/recovery/t/031_recovery_conflict.pl

raw
1 # Copyright (c) 2021-2025, PostgreSQL Global Development Group
2
3 # Test that connections to a hot standby are correctly canceled when a
4 # recovery conflict is detected Also, test that statistics in
5 # pg_stat_database_conflicts are populated correctly
6
7 use strict;
8 use warnings FATAL => 'all';
9 use PostgreSQL::Test::Cluster;
10 use PostgreSQL::Test::Utils;
11 use Test::More;
12
13
14 # Set up nodes
15 my $node_primary = PostgreSQL::Test::Cluster->new('primary');
16 $node_primary->init(allows_streaming => 1);
17
18 my $tablespace1 = "test_recovery_conflict_tblspc";
19
20 $node_primary->append_conf(
21 'postgresql.conf', qq[
22 allow_in_place_tablespaces = on
23 log_temp_files = 0
24
25 # for deadlock test
26 max_prepared_transactions = 10
27
28 # wait some to test the wait paths as well, but not long for obvious reasons
29 max_standby_streaming_delay = 50ms
30
31 temp_tablespaces = $tablespace1
32 # Some of the recovery conflict logging code only gets exercised after
33 # deadlock_timeout. The test doesn't rely on that additional output, but it's
34 # nice to get some minimal coverage of that code.
35 log_recovery_conflict_waits = on
36 deadlock_timeout = 10ms
37 ]);
38 $node_primary->start;
39
40 my $backup_name = 'my_backup';
41
42 $node_primary->safe_psql('postgres',
43 qq[CREATE TABLESPACE $tablespace1 LOCATION '']);
44
45 $node_primary->backup($backup_name);
46 my $node_standby = PostgreSQL::Test::Cluster->new('standby');
47 $node_standby->init_from_backup($node_primary, $backup_name,
48 has_streaming => 1);
49
50 $node_standby->start;
51
52 my $test_db = "test_db";
53
54 # use a new database, to trigger database recovery conflict
55 $node_primary->safe_psql('postgres', "CREATE DATABASE $test_db");
56
57 # test schema / data
58 my $table1 = "test_recovery_conflict_table1";
59 my $table2 = "test_recovery_conflict_table2";
60 $node_primary->safe_psql(
61 $test_db, qq[
62 CREATE TABLE ${table1}(a int, b int);
63 INSERT INTO $table1 SELECT i % 3, 0 FROM generate_series(1,20) i;
64 CREATE TABLE ${table2}(a int, b int);
65 ]);
66 $node_primary->wait_for_replay_catchup($node_standby);
67
68
69 # a longrunning psql that we can use to trigger conflicts
70 my $psql_standby =
71 $node_standby->background_psql($test_db, on_error_stop => 0);
72 my $expected_conflicts = 0;
73
74
75 ## RECOVERY CONFLICT 1: Buffer pin conflict
76 my $sect = "buffer pin conflict";
77 $expected_conflicts++;
78
79 # Aborted INSERT on primary that will be cleaned up by vacuum. Has to be old
80 # enough so that there's not a snapshot conflict before the buffer pin
81 # conflict.
82
83 $node_primary->safe_psql(
84 $test_db,
85 qq[
86 BEGIN;
87 INSERT INTO $table1 VALUES (1,0);
88 ROLLBACK;
89 -- ensure flush, rollback doesn't do so
90 BEGIN; LOCK $table1; COMMIT;
91 ]);
92
93 $node_primary->wait_for_replay_catchup($node_standby);
94
95 my $cursor1 = "test_recovery_conflict_cursor";
96
97 # DECLARE and use a cursor on standby, causing buffer with the only block of
98 # the relation to be pinned on the standby
99 my $res = $psql_standby->query_safe(
100 qq[
101 BEGIN;
102 DECLARE $cursor1 CURSOR FOR SELECT b FROM $table1;
103 FETCH FORWARD FROM $cursor1;
104 ]);
105 # FETCH FORWARD should have returned a 0 since all values of b in the table
106 # are 0
107 like($res, qr/^0$/m, "$sect: cursor with conflicting pin established");
108
109 # to check the log starting now for recovery conflict messages
110 my $log_location = -s $node_standby->logfile;
111
112 # VACUUM FREEZE on the primary
113 $node_primary->safe_psql($test_db, qq[VACUUM FREEZE $table1;]);
114
115 # Wait for catchup. Existing connection will be terminated before replay is
116 # finished, so waiting for catchup ensures that there is no race between
117 # encountering the recovery conflict which causes the disconnect and checking
118 # the logfile for the terminated connection.
119 $node_primary->wait_for_replay_catchup($node_standby);
120
121 check_conflict_log("User was holding shared buffer pin for too long");
122 $psql_standby->reconnect_and_clear();
123 check_conflict_stat("bufferpin");
124
125
126 ## RECOVERY CONFLICT 2: Snapshot conflict
127 $sect = "snapshot conflict";
128 $expected_conflicts++;
129
130 $node_primary->safe_psql($test_db,
131 qq[INSERT INTO $table1 SELECT i, 0 FROM generate_series(1,20) i]);
132 $node_primary->wait_for_replay_catchup($node_standby);
133
134 # DECLARE and FETCH from cursor on the standby
135 $res = $psql_standby->query_safe(
136 qq[
137 BEGIN;
138 DECLARE $cursor1 CURSOR FOR SELECT b FROM $table1;
139 FETCH FORWARD FROM $cursor1;
140 ]);
141 like($res, qr/^0$/m, "$sect: cursor with conflicting snapshot established");
142
143 # Do some HOT updates
144 $node_primary->safe_psql($test_db,
145 qq[UPDATE $table1 SET a = a + 1 WHERE a > 2;]);
146
147 # VACUUM FREEZE, pruning those dead tuples
148 $node_primary->safe_psql($test_db, qq[VACUUM FREEZE $table1;]);
149
150 # Wait for attempted replay of PRUNE records
151 $node_primary->wait_for_replay_catchup($node_standby);
152
153 check_conflict_log(
154 "User query might have needed to see row versions that must be removed");
155 $psql_standby->reconnect_and_clear();
156 check_conflict_stat("snapshot");
157
158
159 ## RECOVERY CONFLICT 3: Lock conflict
160 $sect = "lock conflict";
161 $expected_conflicts++;
162
163 # acquire lock to conflict with
164 $res = $psql_standby->query_safe(
165 qq[
166 BEGIN;
167 LOCK TABLE $table1 IN ACCESS SHARE MODE;
168 SELECT 1;
169 ]);
170 like($res, qr/^1$/m, "$sect: conflicting lock acquired");
171
172 # DROP TABLE containing block which standby has in a pinned buffer
173 $node_primary->safe_psql($test_db, qq[DROP TABLE $table1;]);
174
175 $node_primary->wait_for_replay_catchup($node_standby);
176
177 check_conflict_log("User was holding a relation lock for too long");
178 $psql_standby->reconnect_and_clear();
179 check_conflict_stat("lock");
180
181
182 ## RECOVERY CONFLICT 4: Tablespace conflict
183 $sect = "tablespace conflict";
184 $expected_conflicts++;
185
186 # DECLARE a cursor for a query which, with sufficiently low work_mem, will
187 # spill tuples into temp files in the temporary tablespace created during
188 # setup.
189 $res = $psql_standby->query_safe(
190 qq[
191 BEGIN;
192 SET work_mem = '64kB';
193 DECLARE $cursor1 CURSOR FOR
194 SELECT count(*) FROM generate_series(1,6000);
195 FETCH FORWARD FROM $cursor1;
196 ]);
197 like($res, qr/^6000$/m,
198 "$sect: cursor with conflicting temp file established");
199
200 # Drop the tablespace currently containing spill files for the query on the
201 # standby
202 $node_primary->safe_psql($test_db, qq[DROP TABLESPACE $tablespace1;]);
203
204 $node_primary->wait_for_replay_catchup($node_standby);
205
206 check_conflict_log(
207 "User was or might have been using tablespace that must be dropped");
208 $psql_standby->reconnect_and_clear();
209 check_conflict_stat("tablespace");
210
211
212 ## RECOVERY CONFLICT 5: Deadlock
213 $sect = "startup deadlock";
214 $expected_conflicts++;
215
216 # Want to test recovery deadlock conflicts, not buffer pin conflicts. Without
217 # changing max_standby_streaming_delay it'd be timing dependent what we hit
218 # first
219 $node_standby->adjust_conf(
220 'postgresql.conf',
221 'max_standby_streaming_delay',
222 "${PostgreSQL::Test::Utils::timeout_default}s");
223 $node_standby->restart();
224 $psql_standby->reconnect_and_clear();
225
226 # Generate a few dead rows, to later be cleaned up by vacuum. Then acquire a
227 # lock on another relation in a prepared xact, so it's held continuously by
228 # the startup process. The standby psql will block acquiring that lock while
229 # holding a pin that vacuum needs, triggering the deadlock.
230 $node_primary->safe_psql(
231 $test_db,
232 qq[
233 CREATE TABLE $table1(a int, b int);
234 INSERT INTO $table1 VALUES (1);
235 BEGIN;
236 INSERT INTO $table1(a) SELECT generate_series(1, 100) i;
237 ROLLBACK;
238 BEGIN;
239 LOCK TABLE $table2;
240 PREPARE TRANSACTION 'lock';
241 INSERT INTO $table1(a) VALUES (170);
242 SELECT txid_current();
243 ]);
244
245 $node_primary->wait_for_replay_catchup($node_standby);
246
247 $res = $psql_standby->query_until(
248 qr/^1$/m, qq[
249 BEGIN;
250 -- hold pin
251 DECLARE $cursor1 CURSOR FOR SELECT a FROM $table1;
252 FETCH FORWARD FROM $cursor1;
253 -- wait for lock held by prepared transaction
254 SELECT * FROM $table2;
255 ]);
256 ok(1,
257 "$sect: cursor holding conflicting pin, also waiting for lock, established"
258 );
259
260 # just to make sure we're waiting for lock already
261 ok( $node_standby->poll_query_until(
262 'postgres', qq[
263 SELECT 'waiting' FROM pg_locks WHERE locktype = 'relation' AND NOT granted;
264 ], 'waiting'),
265 "$sect: lock acquisition is waiting");
266
267 # VACUUM FREEZE will prune away rows, causing a buffer pin conflict, while
268 # standby psql is waiting on lock
269 $node_primary->safe_psql($test_db, qq[VACUUM FREEZE $table1;]);
270 $node_primary->wait_for_replay_catchup($node_standby);
271
272 check_conflict_log("User transaction caused buffer deadlock with recovery.");
273 $psql_standby->reconnect_and_clear();
274 check_conflict_stat("deadlock");
275
276 # clean up for next tests
277 $node_primary->safe_psql($test_db, qq[ROLLBACK PREPARED 'lock';]);
278 $node_standby->adjust_conf('postgresql.conf', 'max_standby_streaming_delay',
279 '50ms');
280 $node_standby->restart();
281 $psql_standby->reconnect_and_clear();
282
283
284 # Check that expected number of conflicts show in pg_stat_database. Needs to
285 # be tested before database is dropped, for obvious reasons.
286 is( $node_standby->safe_psql(
287 $test_db,
288 qq[SELECT conflicts FROM pg_stat_database WHERE datname='$test_db';]),
289 $expected_conflicts,
290 qq[$expected_conflicts recovery conflicts shown in pg_stat_database]);
291
292
293 ## RECOVERY CONFLICT 6: Database conflict
294 $sect = "database conflict";
295
296 $node_primary->safe_psql('postgres', qq[DROP DATABASE $test_db;]);
297
298 $node_primary->wait_for_replay_catchup($node_standby);
299
300 check_conflict_log("User was connected to a database that must be dropped");
301
302
303 # explicitly shut down psql instances gracefully - to avoid hangs or worse on
304 # windows
305 $psql_standby->quit;
306
307 $node_standby->stop();
308 $node_primary->stop();
309
310
311 done_testing();
312
313 sub check_conflict_log
314 {
315 my $message = shift;
316 my $old_log_location = $log_location;
317
318 $log_location = $node_standby->wait_for_log(qr/$message/, $log_location);
319
320 cmp_ok($log_location, '>', $old_log_location,
321 "$sect: logfile contains terminated connection due to recovery conflict"
322 );
323 }
324
325 sub check_conflict_stat
326 {
327 my $conflict_type = shift;
328 my $count = $node_standby->safe_psql($test_db,
329 qq[SELECT confl_$conflict_type FROM pg_stat_database_conflicts WHERE datname='$test_db';]
330 );
331
332 is($count, 1, "$sect: stats show conflict on standby");
333 }