public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ian Dick <[email protected]>
To: [email protected]
Subject: Some questions about CREATE INDEX CONCURRENTLY and pg_advisory_lock
Date: Wed, 24 Dec 2025 22:26:26 +1100
Message-ID: <CAJYceS5Kmgd9uxUkb8EfDiATbiVURsCcpW8=stjP+LJ_hoJ+RA@mail.gmail.com> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Some questions about CREATE INDEX CONCURRENTLY and pg_advisory_lock
  In-Reply-To: <CAJYceS5Kmgd9uxUkb8EfDiATbiVURsCcpW8=stjP+LJ_hoJ+RA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox