MIME-Version: 1.0
References: 
 <CADzfLwWFr9h_+cbSQvPpdxgLbVL5wwxFRx21ezNvLYgJM=FVCQ@mail.gmail.com>
 <202604062213.cgo352cdsgsm@alvherre.pgsql>
 <4n4q3preb3lgyhpzstebhux7b2aojhsw7gik4ivaznyggiezrs@lrznutssxlh2>
 <CAA4eK1JDrk9xiALd4DHnGLOkGDbObM59SXSBJyj0_1bNYbr5ng@mail.gmail.com>
 <gebmxzovxumuflknpua4r52tmuiam2odies2qlchzcl36cvphc@iz6bkpk64amp>
 <CADzfLwUed3gmARGbHnsDbrXsqPRW0b0VUtZxi5iNJj0LTC2fJA@mail.gmail.com>
 <CAA4eK1JDd9HBOtR5pgAptcQHpUyXROMe5jqBbLGBRBqn+rCYCg@mail.gmail.com>
 <9539.1775724194@localhost>
 <fpr4nsmyy3mpfrm2mijspr44dgol2cjeke5tyznb4btsznxsgx@iifdbfe2wl63>
In-Reply-To: <fpr4nsmyy3mpfrm2mijspr44dgol2cjeke5tyznb4btsznxsgx@iifdbfe2wl63>
From: Robert Treat <rob@xzilla.net>
Date: Fri, 10 Apr 2026 12:14:59 -0400
Message-ID: 
 <CAJSLCQ2R9uUfP-1kdCBvHYhU_iuKjVpCByViZQ+Qnwan4nDU3w@mail.gmail.com>
Subject: Re: Adding REPACK [concurrently]
To: Andres Freund <andres@anarazel.de>
Cc: Antonin Houska <ah@cybertec.at>, Amit Kapila <amit.kapila16@gmail.com>,
	Mihail Nikalayeu <mihailnikalayeu@gmail.com>,
 Alvaro Herrera <alvherre@alvh.no-ip.org>,
	Srinath Reddy Sadipiralla <srinath2133@gmail.com>,
	Matthias van de Meent <boekewurm+postgres@gmail.com>,
	Pg Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAJSLCQ2R9uUfP-1kdCBvHYhU_iuKjVpCByViZQ%2BQnwan4nDU3w%40mail.gmail.com>
Precedence: bulk

On Thu, Apr 9, 2026 at 10:20=E2=80=AFAM Andres Freund <andres@anarazel.de> =
wrote:
> On 2026-04-09 10:43:14 +0200, Antonin Houska wrote:
> > What Andres proposed (AFAIU) should help to avoid this problem because
> > REPACK's request for AEL would get in front of the VACUUM's request for=
 SUEL
> > in the queue.
>
> Note that that already happens today.
>
> This works today (without the error triggering patch):
>
> S1: REPACK starts
> S2: LOCK TABLE / VACUUM / ... starts waiting
> S1: REPACK tries to get AEL
> S1: REPACK's lock requests get reordered in the wait queue to be before S=
2 and
>     just gets the lock
> S1: REPACK finishes
> S2: lock acquisition completes.
>
> That's because we do already have this "jumping the wait queue" logic, wh=
ich I
> had forgotten about.
>

You know, I was wondering how this wasn't already a problem for
pg_repack/pg_squeeze, and I guess this explains it :-P

>
> What does *not* work is this:
>
> S1: REPACK starts
> S2: BEGIN; SELECT 1 FROM table LIMIT 1;
> S2: LOCK TABLE / VACUUM / ... starts waiting
> S1: REPACK tries to get AEL
> S1: lock is not granted, can't be reordered to be before S2, because S2 h=
olds
>     conflicting lock, deadlock detector triggers
> S2: lock acquisition completes
>
> But with my proposal to properly teach the deadlock detector about assumi=
ng
> there's a wait edge for the eventual lock upgrade by S1, the first exampl=
e
> would still work, because the lock upgrade would not be considered a hard
> cycle, and the second example would have S2 error out.
>

In the above S2 will error out if you try to run a VACUUM, but the
point still stands that calling an explicit LOCK or similar could lead
to this issue. In the current repack world, we document the need for
lock escalation at the end of the repacking and caution that doing
things like DDL or explicit LOCKing could cause trouble, so don't do
that. What you're proposing above would be an improvement though,
IMHO.

>
> > Anti-wraparound (failsafe) VACUUM is a bit different case [1] (i.e. it =
should
> > possibly have higher priority than REPACK), but I think this prioritiza=
tion
> > should be implemented in other way than just letting it get in the way =
of
> > REPACK (at the time REPACK is nearly finished).
>
> Yea, it makes no sense to interrupt the long running repack, given that t=
he
> new relation will have much less stuff for vacuum to do.
>

We might be talking about 2 different scenarios. In the case where we
are at the point of lock escalation, you would probably want the
repack to get priority over a waiting vacuum, even a failsafe vacuum.
But outside of that scenario, we can't know that the repack is the
better option (and statistically it probably isn't) since a repack
that is actively copying rows might still need to rebuild some large
number of indexes (or just some really expensive index) which could
take significantly longer than a failsafe vacuum would need to ensure
wraparound avoidance. I don't think we'd go as far as saying the
failsafe vacuum should cancel the repack, but I think ideally we'd
like it to not be canceled either, since that would increase
likelihood for dba/monitoring to pick up on the situation, and in the
case that REPACK fails for some reason, the failsafe vacuum could
immediately start working without having to go through any additional
hoops.

Robert Treat
https://xzilla.net