MIME-Version: 1.0
From: Ian Dick <ian.dick01@gmail.com>
Date: Wed, 24 Dec 2025 22:26:26 +1100
Message-ID: <CAJYceS5Kmgd9uxUkb8EfDiATbiVURsCcpW8=stjP+LJ_hoJ+RA@mail.gmail.com>
Subject: Some questions about CREATE INDEX CONCURRENTLY and pg_advisory_lock
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000eef64d0646b0ee1d"
Archived-At: <https://www.postgresql.org/message-id/CAJYceS5Kmgd9uxUkb8EfDiATbiVURsCcpW8%3DstjP%2BLJ_hoJ%2BRA%40mail.gmail.com>
Precedence: bulk

--000000000000eef64d0646b0ee1d
Content-Type: text/plain; charset="UTF-8"

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

--000000000000eef64d0646b0ee1d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>I recently noticed some behaviour regarding=C2=A0the =
CREATE INDEX CONCURRENTLY operation, combined with pg_advisory_lock, which =
I found surprising. I&#39;ve looked online for a specific explanation of wh=
at&#39;s happening, but couldn&#39;t seem to find a concrete explanation fo=
r what I&#39;m seeing.</div><div><br></div><div>Here are the steps to repro=
duce:</div><div><br></div><div># from the first terminal</div>$ docker run =
--name some-postgres -e POSTGRES_PASSWORD=3Dpassword -p 5432:5432 -d postgr=
es<br>$ psql postgres://postgres:password@localhost:5432/postgres<br>postgr=
es=3D# CREATE TABLE mytable (mycol varchar(10));<br><br># from the 2nd term=
inal<br>$ psql postgres://postgres:password@localhost:5432/postgres<br>post=
gres=3D# SELECT pg_advisory_lock(1234);<br><br># from the 3rd terminal<br>$=
 psql postgres://postgres:password@localhost:5432/postgres<br>postgres=3D# =
SELECT pg_advisory_lock(1234);<br><br># from the first terminal<br>postgres=
=3D# CREATE INDEX CONCURRENTLY mycol_index ON mytable (mycol);<br><br><div>=
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 ca=
ncel the blocked txn in terminal 3.</div><div><br></div><div>According to t=
he docs on concurrent index creation:</div><div><br></div><div><a href=3D"h=
ttps://www.postgresql.org/docs/current/sql-createindex.html">https://www.po=
stgresql.org/docs/current/sql-createindex.html</a></div><div><br></div><div=
>the 2nd phase of the index creation needs to wait until all current transa=
ction with a snapshot preceding index creation have finished:</div><div><br=
></div><div>&quot;After the second scan, the index build must wait for any =
transactions that have a snapshot (see <a class=3D"gmail-xref" href=3D"http=
s://www.postgresql.org/docs/current/mvcc.html" title=3D"Chapter=C2=A013.=C2=
=A0Concurrency Control">Chapter=C2=A013</a>) predating the second scan to t=
erminate&quot;</div><div><br></div><div>I&#39;m guessing that&#39;s where t=
he 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.</div><div><br></div><div>The reason I&#39;m surprised is th=
at the statement from terminal 3 is just trying to obtain an advisory lock,=
 it&#39;s not trying to access the table in question, or any table. But reg=
ardless, it still seems to block the concurrent index creation. I&#39;m cur=
ious to know if this is the expected behaviour. I&#39;m also curious if it =
might be possible to patch the code to ignore txns like this which should n=
ot interfere with the index creation.</div><div><br></div><div>This is my f=
irst time posting in a Postgres mailing list, apologies if I&#39;m missing =
any of the required details or formatting. Looking forward to learning more=
 about the Postgres internals relevant to this issue.</div><div><br></div><=
div>Thanks</div></div>

--000000000000eef64d0646b0ee1d--