Re: Some questions about CREATE INDEX CONCURRENTLY and pg_advisory_lock

Ian Dick <ian(dot)dick01(at)gmail(dot)com> writes:
> 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.

You did find the explanation:

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

The blocked "SELECT pg_advisory_lock" doesn't hold any interesting
locks, but nonetheless it has a snapshot.

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

Doubt it. You may know that the blocked transaction will not later
try to touch the table being indexed, but Postgres can't really know
that, especially not from within a different session.

regards, tom lane