|
4 | 4 | # recovery conflict is detected Also, test that statistics in |
5 | 5 | # pg_stat_database_conflicts are populated correctly |
6 | 6 |
|
7 | | -# TODO: add a test for deadlock recovery conflicts. |
8 | | - |
9 | 7 | use strict; |
10 | 8 | use warnings; |
11 | 9 | use PostgreSQL::Test::Cluster; |
|
24 | 22 | allow_in_place_tablespaces = on |
25 | 23 | log_temp_files = 0 |
26 | 24 |
|
| 25 | +# for deadlock test |
| 26 | +max_prepared_transactions = 10 |
| 27 | +
|
27 | 28 | # wait some to test the wait paths as well, but not long for obvious reasons |
28 | 29 | max_standby_streaming_delay = 50ms |
29 | 30 |
|
|
55 | 56 |
|
56 | 57 | # test schema / data |
57 | 58 | my$table1 ="test_recovery_conflict_table1"; |
58 | | -$node_primary->safe_psql($test_db,qq[CREATE TABLE${table1}(a int, b int);]); |
59 | | -$node_primary->safe_psql($test_db, |
60 | | -qq[INSERT INTO$table1 SELECT i % 3, 0 FROM generate_series(1,20) i]); |
| 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 | +]); |
61 | 66 | my$primary_lsn =$node_primary->lsn('flush'); |
62 | 67 | $node_primary->wait_for_catchup($node_standby,'replay',$primary_lsn); |
63 | 68 |
|
|
217 | 222 | check_conflict_stat("tablespace"); |
218 | 223 |
|
219 | 224 |
|
| 225 | +## RECOVERY CONFLICT 5: Deadlock |
| 226 | +$sect ="startup deadlock"; |
| 227 | +$expected_conflicts++; |
| 228 | + |
| 229 | +# Generate a few dead rows, to later be cleaned up by vacuum. Then acquire a |
| 230 | +# lock on another relation in a prepared xact, so it's held continuously by |
| 231 | +# the startup process. The standby psql will block acquiring that lock while |
| 232 | +# holding a pin that vacuum needs, triggering the deadlock. |
| 233 | +$node_primary->safe_psql( |
| 234 | +$test_db, |
| 235 | +qq[ |
| 236 | +CREATE TABLE$table1(a int, b int); |
| 237 | +INSERT INTO$table1 VALUES (1); |
| 238 | +BEGIN; |
| 239 | +INSERT INTO$table1(a) SELECT generate_series(1, 100) i; |
| 240 | +ROLLBACK; |
| 241 | +BEGIN; |
| 242 | +LOCK TABLE$table2; |
| 243 | +PREPARE TRANSACTION 'lock'; |
| 244 | +INSERT INTO$table1(a) VALUES (170); |
| 245 | +SELECT txid_current(); |
| 246 | +]); |
| 247 | + |
| 248 | +$primary_lsn =$node_primary->lsn('flush'); |
| 249 | +$node_primary->wait_for_catchup($node_standby,'replay',$primary_lsn); |
| 250 | + |
| 251 | +$psql_standby{stdin} .=qq[ |
| 252 | + BEGIN; |
| 253 | + -- hold pin |
| 254 | + DECLARE$cursor1 CURSOR FOR SELECT a FROM$table1; |
| 255 | + FETCH FORWARD FROM$cursor1; |
| 256 | + -- wait for lock held by prepared transaction |
| 257 | +SELECT * FROM$table2; |
| 258 | +]; |
| 259 | +ok( pump_until( |
| 260 | +$psql_standby{run},$psql_timeout, |
| 261 | +\$psql_standby{stdout},qr/^1$/m,), |
| 262 | +"$sect: cursor holding conflicting pin, also waiting for lock, established" |
| 263 | +); |
| 264 | + |
| 265 | +# just to make sure we're waiting for lock already |
| 266 | +ok($node_standby->poll_query_until( |
| 267 | +'postgres',qq[ |
| 268 | +SELECT 'waiting' FROM pg_locks WHERE locktype = 'relation' AND NOT granted; |
| 269 | +],'waiting'), |
| 270 | +"$sect: lock acquisition is waiting"); |
| 271 | + |
| 272 | +# VACUUM will prune away rows, causing a buffer pin conflict, while standby |
| 273 | +# psql is waiting on lock |
| 274 | +$node_primary->safe_psql($test_db,qq[VACUUM$table1;]); |
| 275 | +$primary_lsn =$node_primary->lsn('flush'); |
| 276 | +$node_primary->wait_for_catchup($node_standby,'replay',$primary_lsn); |
| 277 | + |
| 278 | +check_conflict_log("User transaction caused buffer deadlock with recovery."); |
| 279 | +reconnect_and_clear(); |
| 280 | +check_conflict_stat("deadlock"); |
| 281 | + |
| 282 | +# clean up for next tests |
| 283 | +$node_primary->safe_psql($test_db,qq[ROLLBACK PREPARED 'lock';]); |
| 284 | + |
| 285 | + |
220 | 286 | # Check that expected number of conflicts show in pg_stat_database. Needs to |
221 | 287 | # be tested before database is dropped, for obvious reasons. |
222 | 288 | is($node_standby->safe_psql( |
|
226 | 292 | qq[$expected_conflicts recovery conflicts shown in pg_stat_database]); |
227 | 293 |
|
228 | 294 |
|
229 | | -## RECOVERY CONFLICT5: Database conflict |
| 295 | +## RECOVERY CONFLICT6: Database conflict |
230 | 296 | $sect ="database conflict"; |
231 | 297 |
|
232 | 298 | $node_primary->safe_psql('postgres',qq[DROP DATABASE$test_db;]); |
@@ -259,7 +325,13 @@ sub pump_until_standby |
259 | 325 |
|
260 | 326 | subreconnect_and_clear |
261 | 327 | { |
262 | | -$psql_standby{stdin} .="\\q\n"; |
| 328 | +# If psql isn't dead already, tell it to quit as \q, when already dead, |
| 329 | +# causes IPC::Run to unhelpfully error out with "ack Broken pipe:". |
| 330 | +$psql_standby{run}->pump_nb(); |
| 331 | +if ($psql_standby{run}->pumpable()) |
| 332 | +{ |
| 333 | +$psql_standby{stdin} .="\\q\n"; |
| 334 | +} |
263 | 335 | $psql_standby{run}->finish; |
264 | 336 |
|
265 | 337 | # restart |
|