From: Antonin Houska <ah@cybertec.at>
To: Thom Brown <thom@linux.com>
cc: shihao zhong <zhong950419@gmail.com>,
    Manu <manuelreyesbravo@gmail.com>,
    pgsql-hackers@lists.postgresql.org
Subject: Re: REPACK (CONCURRENTLY) can silently lose updates when the toast
 table is rewritten
In-reply-to: 
 <CAA-aLv55NFuBYOuHcbPBfPJYvfayV2rrwQmbe6CRY1hmy6FLCw@mail.gmail.com>
References: 
 <CAA-aLv5MF6BLL+BWvix2Yw+CBardtH43AofPReQunhDZPNBtuA@mail.gmail.com>
 <179012413951.1850281.5077495683381671561@gmail.com>
 <CAGRkXqRYLtBRaMzdH+e7PMO-BRaWPPo37gvOx3C=jQ1uP4Cx7w@mail.gmail.com>
 <47479.1790180571@localhost>
 <CAA-aLv55NFuBYOuHcbPBfPJYvfayV2rrwQmbe6CRY1hmy6FLCw@mail.gmail.com>
Comments: In-reply-to Thom Brown <thom@linux.com>
   message dated "Wed, 23 Sep 2026 18:18:20 +0100."
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <8576.1790236672.1@localhost>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 24 Sep 2026 09:57:52 +0200
Message-ID: <8577.1790236672@localhost>
Archived-At: 
 <https://www.postgresql.org/message-id/8577.1790236672%40localhost>
Precedence: bulk

Thom Brown <thom@linux.com> wrote:

> On Wed, 23 Sept 2026 at 17:22, Antonin Houska <ah@cybertec.at> wrote:
> >
> > shihao zhong <zhong950419@gmail.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 wo=
rker
> > > waits for all XIDs while it sets up.
> >
> > The same (supposedly low) deadlock risk already exists for the main ta=
ble, 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 it=
s
> >      * 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 situ=
ation
> > worse.
> >
> > The reason TOAST relation is not locked until copy_table_data() does s=
o is
> > that CLUSTER / VACUUM FULL in v18 did it this way (not sure what the r=
eason
> > for such design was). I haven't changed that for REPACK exactly becaus=
e I
> > failed to envision this stale relfilenode issue.
> =

> I gave that a try, and it does. It just swaps the lost update for a dead=
lock.
> =

> 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. Howeve=
r,
where would the request for AccessExclusiveLock come from in that case? CR=
EATE
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?

-- =

Antonin Houska
Web: https://www.cybertec-postgresql.com