8000 Fix deadlock hazard in CREATE INDEX CONCURRENTLY · postgrespro/postgres@54eff53 · GitHub
[go: up one dir, main page]
Skip to content

Commit 54eff53

Browse files
alvherrealvherre
committed
Fix deadlock hazard in CREATE INDEX CONCURRENTLY
Multiple sessions doing CREATE INDEX CONCURRENTLY simultaneously are supposed to be able to work in parallel, as evidenced by fixes in commit c3d09b3 specifically to support this case. In reality, one of the sessions would be aborted by a misterious "deadlock detected" error. Jeff Janes diagnosed that this is because of leftover snapshots used for system catalog scans -- this was broken by 8aa3e47 keeping track of (registering) the catalog snapshot. To fix the deadlocks, it's enough to de-register that snapshot prior to waiting. Backpatch to 9.4, which introduced MVCC catalog scans. Include an isolationtester spec that 8 out of 10 times reproduces the deadlock with the unpatched code for me (Álvaro). Author: Jeff Janes Diagnosed-by: Jeff Janes Reported-by: Jeremy Finzel Discussion: https://postgr.es/m/CAMa1XUhHjCv8Qkx0WOr1Mpm_R4qxN26EibwCrj0Oor2YBUFUTg%40mail.gmail.com
1 parent 4380362 commit 54eff53

File tree

4 files changed

+63
-0
lines changed

4 files changed

+63
-0
lines changed

‎src/backend/commands/indexcmds.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -856,11 +856,14 @@ DefineIndex(Oid relationId,
856856
* doing CREATE INDEX CONCURRENTLY, which would see our snapshot as one
857857
* they must wait for. But first, save the snapshot's xmin to use as
858858
* limitXmin for GetCurrentVirtualXIDs().
859+
*
860+
* Our catalog snapshot could have the same effect, so drop that one too.
859861
*/
860862
limitXmin = snapshot->xmin;
861863

862864
PopActiveSnapshot();
863865
UnregisterSnapshot(snapshot);
866+
InvalidateCatalogSnapshot();
864867

865868
/*
866869
* The index is now valid in the sense that it contains all currently

‎src/test/isolation/expected/multiple-cic.out

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
Parsed test spec with 2 sessions
2+
3+
starting permutation: s2l s1i s2i
4+
step s2l: SELECT pg_advisory_lock(281457);
5+
pg_advisory_lock
6+
7+
8+
step s1i:
9+
CREATE INDEX CONCURRENTLY mcic_one_pkey ON mcic_one (id)
10+
WHERE lck_shr(281457);
11+
<waiting ...>
12+
step s2i:
13+
CREATE INDEX CONCURRENTLY mcic_two_pkey ON mcic_two (id)
14+
WHERE unlck();
15+
16+
step s1i: <... completed>
17+
s1
18+
19+

‎src/test/isolation/isolation_schedule

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -55,6 +55,7 @@ test: skip-locked-2
5555
test: skip-locked-3
5656
test: skip-locked-4
5757
test: drop-index-concurrently-1
58+
test: multiple-cic
5859
test: alter-table-1
5960
test: alter-table-2
6061
test: alter-table-3

‎src/test/isolation/specs/multiple-cic.spec

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,40 @@
1+
# Test multiple CREATE INDEX CONCURRENTLY working simultaneously
2+
3+
setup
4+
{
5+
CREATE TABLE mcic_one (
6+
id int
7+
);
8+
CREATE TABLE mcic_two (
9+
id int
10+
);
11+
CREATE FUNCTION lck_shr(bigint) RETURNS bool IMMUTABLE LANGUAGE plpgsql AS $$
12+
BEGIN PERFORM pg_advisory_lock_shared($1); RETURN true; END;
13+
$$;
14+
CREATE FUNCTION unlck() RETURNS bool IMMUTABLE LANGUAGE plpgsql AS $$
15+
BEGIN PERFORM pg_advisory_unlock_all(); RETURN true; END;
16+
$$;
17+
}
18+
teardown
19+
{
20+
DROP TABLE mcic_one, mcic_two;
21+
DROP FUNCTION lck_shr(bigint);
22+
DROP FUNCTION unlck();
23+
}
24+
25+
session "s1"
26+
step "s1i" {
27+
CREATE INDEX CONCURRENTLY mcic_one_pkey ON mcic_one (id)
28+
WHERE lck_shr(281457);
29+
}
30+
teardown { SELECT pg_advisory_unlock_all() AS "s1"; }
31+
32+
33+
session "s2"
34+
step "s2l" { SELECT pg_advisory_lock(281457); }
35+
step "s2i" {
36+
CREATE INDEX CONCURRENTLY mcic_two_pkey ON mcic_two (id)
37+
WHERE unlck();
38+
}
39+
40+
permutation 31EC "s2l" "s1i" "s2i"

0 commit comments

Comments
 (0)
0