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

From: Thom Brown <thom(at)linux(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: REPACK (CONCURRENTLY) can silently lose updates when the toast table is rewritten
Date: 2026-09-23 00:23:11
Message-ID: CAA-aLv5MF6BLL+BWvix2Yw+CBardtH43AofPReQunhDZPNBtuA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

Whilst stress-testing REPACK (CONCURRENTLY) I managed to get it to silently
throw away committed updates to a TOASTed column. There's no error, and both
verify_heapam() and bt_index_check() seem to think everything is fine.

I had claude analyse what's going on and:
-------
It happens at the very start of REPACK. The decoding worker grabs the
toast relation's relfilenode in repack_setup_logical_decoding() and immediately
lets go of the lock, but the backend doesn't lock the toast table until
copy_table_data(). In between sits get_initial_snapshot(), which waits for all
running XIDs to finish, so an open transaction widens the gap nicely. During
that gap you can rewrite the toast table by name (VACUUM FULL / REPACK /
CLUSTER of it) and give it a fresh relfilenode - the parent is only held with
ShareUpdateExclusiveLock, so that's allowed.
-------

Reproducible steps:

-- session A: hold a transaction open so REPACK blocks building its snapshot
BEGIN;
SELECT pg_current_xact_id();

-- session B: set up, arm the one stock injection point, then REPACK (blocks)
CREATE TABLE test (id int PRIMARY KEY, big text);
ALTER TABLE test ALTER COLUMN big SET STORAGE EXTERNAL;
INSERT INTO test SELECT g, repeat('old', 3000) FROM generate_series(1,3) g;
SELECT injection_points_attach('repack-concurrently-before-lock', 'wait');
REPACK (CONCURRENTLY) test;

-- session C: rewrite the toast table in the gap, then let A commit
VACUUM FULL pg_toast.pg_toast_<oid-of-test>;

-- session A:
COMMIT;

-- session C: update the toasted rows
UPDATE test SET big = repeat('NEW', 4000) WHERE id IN (1,2,3);

SELECT id, left(big,9) AS val, length(big) FROM test ORDER BY id;
id | val | length
----+-----------+--------
1 | NEWNEWNEW | 12000
2 | NEWNEWNEW | 12000
3 | NEWNEWNEW | 12000

-- session C: let REPACK finish, then look again
SELECT injection_points_wakeup('repack-concurrently-before-lock');

SELECT id, left(big,9) AS val, length(big) FROM test ORDER BY id;
id | val | length
----+-----------+--------
1 | oldoldold | 9000
2 | oldoldold | 9000
3 | oldoldold | 9000

SELECT * FROM verify_heapam('t', check_toast => true); -- 0 rows
SELECT bt_index_check('t_pkey', heapallindexed => true); -- passes

So three committed updates have quietly reverted to their old values.

Regards

Thom

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Manu 2026-09-23 00:27:58 Re: PROXY protocol support
Previous Message Manu 2026-09-23 00:15:41 Re: generic plans and "initial" pruning