Re: REPACK (CONCURRENTLY) can silently lose updates when the toast table is rewritten

On Thu, 24 Sept 2026 at 09:32, Thom Brown <thom(at)linux(dot)com> wrote:
>
> On Thu, 24 Sept 2026 at 08:57, Antonin Houska <ah(at)cybertec(dot)at> wrote:
> >
> > Thom Brown <thom(at)linux(dot)com> wrote:
> >
> > > On Wed, 23 Sept 2026 at 17:22, Antonin Houska <ah(at)cybertec(dot)at> wrote:
> > > >
> > > > shihao zhong <zhong950419(at)gmail(dot)com> wrote:
> > > >
> > > > > > or whether the relfilenode should be re-checked after the snapshot is built
> > > > >
> > > > > Holding the toast lock from the start deadlocks. A session that asks for
> > > > > AccessExclusiveLock gets an XID before it waits, and the decoding worker
> > > > > waits for all XIDs while it sets up.
> > > >
> > > > The same (supposedly low) deadlock risk already exists for the main table, see
> > > > this comment in rebuild_relation():
> > > >
> > > > /*
> > > > * Start the worker that decodes data changes applied while we're
> > > > * copying the table contents.
> > > > *
> > > > * Note that the worker has to wait for all transactions with XID
> > > > * already assigned to finish. If some of those transactions is
> > > > * waiting for a lock conflicting with ShareUpdateExclusiveLock on our
> > > > * table (e.g. it runs CREATE INDEX), we can end up in a deadlock.
> > > > * Not sure this risk is worth unlocking/locking the table (and its
> > > > * clustering index) and checking again if it's still eligible for
> > > > * REPACK CONCURRENTLY.
> > > > */
> > > > start_repack_decoding_worker(tableOid);
> > > >
> > > > I'm not sure if locking the TOAST relation earlier would make the situation
> > > > worse.
> > > >
> > > > The reason TOAST relation is not locked until copy_table_data() does so is
> > > > that CLUSTER / VACUUM FULL in v18 did it this way (not sure what the reason
> > > > for such design was). I haven't changed that for REPACK exactly because I
> > > > failed to envision this stale relfilenode issue.
> > >
> > > I gave that a try, and it does. It just swaps the lost update for a deadlock.
> > >
> > > If you lock the toast up front and something rewrites it at the same
> > > time (which is the thing that triggers this in the first place, e.g. a
> > > REPACK of the toast table), REPACK falls over:
> > >
> > > Session 1:
> > > BEGIN;
> > > INSERT INTO test VALUES (999999, 'x');
> > >
> > > Session 2:
> > > REPACK (CONCURRENTLY) test;
> > >
> > > Session 1:
> > > CREATE INDEX ON test (big);
> > >
> > > ERROR: deadlock detected
> > > DETAIL: Process 214534 waits for ShareLock on transaction 1774005;
> > > blocked by process 214579.
> > > Process 214579 waits for AccessExclusiveLock on relation 3672470 of
> > > database 5; blocked by process 214534.
> > > CONTEXT: REPACK decoding worker
> > >
> > > The rewrite already has an XID by the time it waits, and the worker
> > > waits for that XID whilst it sets up, so the two just sit on each
> > > other. It doesn't matter which lock we take either because anything
> > > that would stop the rewrite conflicts with it.
> >
> > IMO this example does not exactly demonstrate the problem described in the
> > comment above: if REPACK (CONCURRENTLY) waits for AccessExclusiveLock, it's
> > going to perform the relation swap, so the worker should already be gone.
> >
> > On the other hand, the message
> >
> > "Process ... waits for ShareLock on transaction ..."
> >
> > is what the deadlock detector would report for the decoding worker. However,
> > where would the request for AccessExclusiveLock come from in that case? CREATE
> > INDEX only uses it to lock the new index relation, however that cannot be
> > locked by other backends until the transaction has committed (because it's not
> > visible before commit).
> >
> > What exactly have you changed in the code?
>
> Apologies, I seem to have incorrectly paired tests with different
> results during my copy and pasting.
>
> I'll see if I can untangle it later today.

I'd run two separate tests and pasted the error from one under the
steps of the other.

Okay, if I start from scratch:

Session 1:
CREATE TABLE test (id int PRIMARY KEY, big text);

BEGIN;
INSERT INTO test VALUES (999999, 'x');

Session 2:
REPACK (CONCURRENTLY) test;

Session 1:
CREATE INDEX ON test (big);

ERROR: deadlock detected
DETAIL: Process 1172013 waits for ShareLock on relation 3672610 of
database 5; blocked by process 1172091.
Process 1172091 waits for ShareLock on transaction 1774055; blocked by
process 1172013.

Regards

Thom