Deadlock when running CREATE INDEX CONCURRENTLY while another process is waiting for advisory lock

[ThomWright]

See flyway/flyway#1654 for another instance of this problem.

If >1 process is trying to run migrations concurrently, process A will have an advisory lock and be running migrations, and process B will be waiting for that advisory lock.

If process A tries to CREATE INDEX CONCURRENTLY, then it will block on process B for some reason. Since process B is blocked on process A releasing the lock, this is a deadlock.

Example PG error message (stolen from flyway issue above):

ERROR:  deadlock detected
DETAIL:  Process 587 waits for ShareLock on virtual transaction 10/12; blocked by process 588.
        Process 588 waits for ExclusiveLock on advisory lock [47999,18028,1462734915,1]; blocked by process 587.
        Process 587: CREATE INDEX CONCURRENTLY idx_cities_name ON cities(name)
        Process 588: SELECT pg_advisory_lock(77431133147203)
HINT:  See server log for query details.

This seems like a similar issue which was a PostgreSQL bug which got fixed. I'm wondering if this is a similar bug.

[When creating the index, PostgreSQL] must wait for all existing transactions that could potentially modify or use the index to terminate

• PostgreSQL docs

Does this suggest that the 'virtual transaction' waiting for the advisory lock is considered to potentially modify or use the index? That doesn't seem right.

I have reproduced this on 9.4 (default version this library is tested on), 9.6.16 and 12.1.

[ThomWright]

For reference, flyway fixed this by polling with pg_try_advisory_lock instead of blocking on pg_advisory_lock.

This seems like the quick fix for now, but I'd like to investigate why this happens.