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

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.

Thom