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