On Thu, 24 Sept 2026 at 09:32, Thom Brown <[email protected]> wrote: > > On Thu, 24 Sept 2026 at 08:57, Antonin Houska <[email protected]> wrote: > > > > Thom Brown <[email protected]> wrote: > > > > > On Wed, 23 Sept 2026 at 17:22, Antonin Houska <[email protected]> wrote: > > > > > > > > shihao zhong <[email protected]> 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
