public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Ian Dick <[email protected]>
Cc: [email protected]
Subject: Re: Some questions about CREATE INDEX CONCURRENTLY and pg_advisory_lock
Date: Fri, 26 Dec 2025 23:03:27 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAJYceS5Kmgd9uxUkb8EfDiATbiVURsCcpW8=stjP+LJ_hoJ+RA@mail.gmail.com>
References: <CAJYceS5Kmgd9uxUkb8EfDiATbiVURsCcpW8=stjP+LJ_hoJ+RA@mail.gmail.com>

Ian Dick <[email protected]> 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

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], [email protected]
  Subject: Re: Some questions about CREATE INDEX CONCURRENTLY and pg_advisory_lock
  In-Reply-To: <[email protected]>

* 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