| 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 |
} |