I recently noticed some behaviour regarding the CREATE INDEX CONCURRENTLY operation, combined with pg_advisory_lock, which I found surprising. I've looked online for a specific explanation of what's happening, but couldn't seem to find a concrete explanation for what I'm seeing.
Here are the steps to reproduce: # from the first terminal $ docker run --name some-postgres -e POSTGRES_PASSWORD=password -p 5432:5432 -d postgres $ psql postgres://postgres:password@localhost:5432/postgres postgres=# CREATE TABLE mytable (mycol varchar(10)); # from the 2nd terminal $ psql postgres://postgres:password@localhost:5432/postgres postgres=# SELECT pg_advisory_lock(1234); # from the 3rd terminal $ psql postgres://postgres:password@localhost:5432/postgres postgres=# SELECT pg_advisory_lock(1234); # from the first terminal postgres=# CREATE INDEX CONCURRENTLY mycol_index ON mytable (mycol); At this point the index creation is blocked on the txn from terminal 3 (the blocked pg_advisory_lock(1234)). To unblock the index creation, you can cancel the blocked txn in terminal 3. According to the docs on concurrent index creation: https://www.postgresql.org/docs/current/sql-createindex.html the 2nd phase of the index creation needs to wait until all current transaction with a snapshot preceding index creation have finished: "After the second scan, the index build must wait for any transactions that have a snapshot (see Chapter 13 <https://www.postgresql.org/docs/current/mvcc.html>) predating the second scan to terminate" I'm guessing that's where the index creation in my example is getting stuck, and the txn from terminal 3 is flagged by the index creation as a txn with a snapshot predating the index creation. The reason I'm surprised is that the statement from terminal 3 is just trying to obtain an advisory lock, it's not trying to access the table in question, or any table. But regardless, it still seems to block the concurrent index creation. I'm curious to know if this is the expected behaviour. I'm also curious if it might be possible to patch the code to ignore txns like this which should not interfere with the index creation. This is my first time posting in a Postgres mailing list, apologies if I'm missing any of the required details or formatting. Looking forward to learning more about the Postgres internals relevant to this issue. Thanks
