agora inbox for pgsql-hackers@postgresql.org  

help / color / mirror / Atom feed

From: Antonin Houska <ah@cybertec.at>
To: shihao zhong <zhong950419@gmail.com>
Cc: Manu <manuelreyesbravo@gmail.com>
Cc: Thom Brown <thom@linux.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: REPACK (CONCURRENTLY) can silently lose updates when the toast table is rewritten
Date: Wed, 23 Sep 2026 18:22:51 +0200
Message-ID: <47479.1790180571@localhost> (raw)
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>

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 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.

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

view thread (17+ messages)  latest in thread

Message-ID: <47479.1790180571@localhost>
Permalink:  ../47479.1790180571@localhost/
Also on:    postgresql.org/message-id/47479.1790180571@localhost

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: ah@cybertec.at, zhong950419@gmail.com, manuelreyesbravo@gmail.com, 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: <47479.1790180571@localhost>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox