MIME-Version: 1.0
References: 
 <CAFC+b6pK9ogeSpMA8hg18XhC1eNPcsKWBwoC5OySXi4iTxwtRw@mail.gmail.com>
 <202603252005.quy5h4oipoxd@alvherre.pgsql>
In-Reply-To: <202603252005.quy5h4oipoxd@alvherre.pgsql>
From: Srinath Reddy Sadipiralla <srinath2133@gmail.com>
Date: Tue, 31 Mar 2026 23:07:17 +0530
Message-ID: 
 <CAFC+b6of6_poBQ6EgK8N49VQwAUYX=uLkHiU-TP7y+DamBD5TQ@mail.gmail.com>
Subject: Re: Adding REPACK [concurrently]
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Mihail Nikalayeu <mihailnikalayeu@gmail.com>,
 Antonin Houska <ah@cybertec.at>,
	Matthias van de Meent <boekewurm+postgres@gmail.com>,
	Pg Hackers <pgsql-hackers@lists.postgresql.org>,
 Robert Treat <rob@xzilla.net>
Content-Type: multipart/alternative; boundary="000000000000da1049064e556bf0"
Archived-At: 
 <https://www.postgresql.org/message-id/CAFC%2Bb6of6_poBQ6EgK8N49VQwAUYX%3DuLkHiU-TP7y%2BDamBD5TQ%40mail.gmail.com>
Precedence: bulk

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

Hi Alvaro,

On Thu, Mar 26, 2026 at 1:42=E2=80=AFAM Alvaro Herrera <alvherre@alvh.no-ip=
.org>
wrote:

>
> As for lock upgrade, I wonder if the best way to handle this isn't to
> hack the deadlock detector so that it causes any *other* process to die,
> if they detect that they would block on REPACK.  Arguably there's
> nothing that you can do to a table while its undergoing REPACK
> CONCURRENTLY; any alterations would have to wait until the repacking is
> compelted.  We can implement that idea simply enough, as shown in this
> crude prototype.
>

After testing this, I observed that it solves the scenario where a query is
waiting
on REPACK. For example, if a DROP TABLE requests an AEL and queues
behind REPACK's ShareUpdateExclusiveLock, the deadlock detector comes
when REPACK tries to upgrade to AEL, killing the DROP to prevent the
circular
queue deadlock, But the case I originally mentioned [1] was the reverse:
what
happens if a transaction already holds a lock that conflicts with the
upcoming
AEL upgrade (e.g., an analytical SELECT or an idle-in-transaction holding
an AccessShareLock),
but isn't waiting on REPACK at all?

In this case, there's no circular wait. The deadlock detector never fires.
REPACK
simply queues behind the SELECT, eventually hits its lock_timeout, aborts
and
cleans up.Initially, I thought this cleanup was expected behavior. But
after seeing
your solution to protect REPACK from losing its transient table work, I
thought it's "not expected".
If the goal is to prevent REPACK's work from being wasted, should we error
out
the backend that is making REPACK wait during the final swap phase? I am
thinking
of something conceptually similar to
ResolveRecoveryConflictWithLock,actively
cancelling the conflicting session to allow the AEL upgrade to proceed.
Thoughts?


test scenario:

session 1:
postgres=3D# repack (concurrently) stress_victim;
had a breakpoint rebuild_relation_finish_concurrent->
LockRelationOid(old_table_oid, AccessExclusiveLock); just before getting
the exclusive lock.
with lock_timeout =3D 5s


session 2:
postgres=3D# BEGIN;
SELECT * FROM stress_victim LIMIT 1;
-- left it open
BEGIN
 id  | balance |
           payload
-----+---------+---------------------------------
-------------------------------------------------
-------------------------------------------------
-------------------------------------------------
--------------
 170 |      65 | d12f400c4d0d3c49818f88597e16cf29
d12f400c4d0d3c49818f88597e16cf29d12f400c4d0d3c498
18f88597e16cf29d12f400c4d0d3c49818f88597e16cf29d1
2f400c4d0d3c49818f88597e16cf29d12f400c4d0d3c49818
f88597e16cf29
(1 row)
-- this gets us a conflicting lock (AccessShareLock) on the same table,
REPACK (concurrently) is running on.


session 1:
release the breakpoint and now the backend waits for the conflicting lock
to be released.
in between if lock_timeout occurs then transaction aborts.
postgres=3D# repack (concurrently) stress_victim;
ERROR:  canceling statement due to lock timeout
CONTEXT:  waiting for AccessExclusiveLock on relation 16637 of database 5

[1] -
https://www.postgresql.org/message-id/CAFC%2Bb6pK9ogeSpMA8hg18XhC1eNPcsKWBw=
oC5OySXi4iTxwtRw%40mail.gmail.com

--=20
Thanks,
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/

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

<div dir=3D"ltr"><div dir=3D"ltr">Hi Alvaro,</div><br><div class=3D"gmail_q=
uote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, M=
ar 26, 2026 at 1:42=E2=80=AFAM Alvaro Herrera &lt;<a href=3D"mailto:alvherr=
e@alvh.no-ip.org">alvherre@alvh.no-ip.org</a>&gt; wrote:<br></div><blockquo=
te class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px =
solid rgb(204,204,204);padding-left:1ex"><br>
As for lock upgrade, I wonder if the best way to handle this isn&#39;t to<b=
r>
hack the deadlock detector so that it causes any *other* process to die,<br=
>
if they detect that they would block on REPACK.=C2=A0 Arguably there&#39;s<=
br>
nothing that you can do to a table while its undergoing REPACK<br>
CONCURRENTLY; any alterations would have to wait until the repacking is<br>
compelted.=C2=A0 We can implement that idea simply enough, as shown in this=
<br>
crude prototype.=C2=A0<br></blockquote><div><br>After testing this, I obser=
ved that it solves the scenario where a query is waiting<br>on REPACK. For =
example, if a DROP TABLE requests an AEL and queues<br>behind REPACK&#39;s =
ShareUpdateExclusiveLock, the deadlock detector comes<br>when REPACK tries =
to upgrade to AEL, killing the DROP to prevent the circular<br>queue deadlo=
ck, But=C2=A0the case I originally mentioned [1] was the reverse: what<br>h=
appens if a transaction already holds a lock that conflicts with the upcomi=
ng<br>AEL upgrade (e.g., an analytical SELECT or an idle-in-transaction hol=
ding an AccessShareLock),<br>but isn&#39;t waiting on REPACK at all?<br><br=
>In this case, there&#39;s no circular wait. The deadlock detector never fi=
res. REPACK<br>simply queues behind the SELECT, eventually hits its lock_ti=
meout, aborts and<br>cleans up.Initially, I thought this cleanup was expect=
ed behavior. But after seeing<br>your solution to protect REPACK from losin=
g its transient table work, I thought it&#39;s &quot;not expected&quot;.<br=
>If the goal is to prevent REPACK&#39;s work from being wasted, should we e=
rror out<br>the backend that is making REPACK wait during the final swap ph=
ase? I am thinking<br>of something conceptually similar to ResolveRecoveryC=
onflictWithLock,actively<br>cancelling the conflicting session to allow the=
 AEL upgrade to proceed. Thoughts?</div><div><br><br><br>test scenario:<br>=
<br>session 1:<br>postgres=3D# repack (concurrently) stress_victim;<br>had =
a breakpoint rebuild_relation_finish_concurrent-&gt;<br>LockRelationOid(old=
_table_oid, AccessExclusiveLock); just before getting<br>the exclusive lock=
.<br>with lock_timeout =3D 5s<br><br><br><br>session 2:<br>postgres=3D# BEG=
IN;<br>SELECT * FROM stress_victim LIMIT 1;<br>-- left it open<br>BEGIN<br>=
=C2=A0id =C2=A0| balance |<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0payl=
oad<br>-----+---------+---------------------------------<br>---------------=
----------------------------------<br>-------------------------------------=
------------<br>-------------------------------------------------<br>------=
--------<br>=C2=A0170 | =C2=A0 =C2=A0 =C2=A065 | d12f400c4d0d3c49818f88597e=
16cf29<br>d12f400c4d0d3c49818f88597e16cf29d12f400c4d0d3c498<br>18f88597e16c=
f29d12f400c4d0d3c49818f88597e16cf29d1<br>2f400c4d0d3c49818f88597e16cf29d12f=
400c4d0d3c49818<br>f88597e16cf29<br>(1 row)<br>-- this gets us a conflictin=
g lock (AccessShareLock) on the same table,<br>REPACK (concurrently) is run=
ning on.<br><br><br><br>session 1:<br>release the breakpoint and now the ba=
ckend waits for the conflicting lock<br>to be released.<br>in between if lo=
ck_timeout occurs then transaction aborts.<br>postgres=3D# repack (concurre=
ntly) stress_victim;<br>ERROR: =C2=A0canceling statement due to lock timeou=
t<br>CONTEXT: =C2=A0waiting for AccessExclusiveLock on relation 16637 of da=
tabase 5<br><br></div></div><div>[1] -=C2=A0<a href=3D"https://www.postgres=
ql.org/message-id/CAFC%2Bb6pK9ogeSpMA8hg18XhC1eNPcsKWBwoC5OySXi4iTxwtRw%40m=
ail.gmail.com">https://www.postgresql.org/message-id/CAFC%2Bb6pK9ogeSpMA8hg=
18XhC1eNPcsKWBwoC5OySXi4iTxwtRw%40mail.gmail.com</a></div><div><br></div><s=
pan class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=
=3D"gmail_signature"><div dir=3D"ltr"><span style=3D"color:rgb(34,34,34)">T=
hanks,</span><br style=3D"color:rgb(34,34,34)"><span style=3D"color:rgb(34,=
34,34)">Srinath Reddy Sadipiralla</span><br style=3D"color:rgb(34,34,34)"><=
span style=3D"color:rgb(34,34,34)">EDB:=C2=A0</span><a href=3D"https://www.=
enterprisedb.com/" style=3D"color:rgb(17,85,204)" target=3D"_blank">https:/=
/www.enterprisedb.com/</a></div></div></div>

--000000000000da1049064e556bf0--