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

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 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 situatio=
n
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 reaso=
n
for such design was). I haven't changed that for REPACK exactly because I
failed to envision this stale relfilenode issue.

-- =

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