VACUUM FULL or CREATE INDEX fails with error: missing chunk number 0 for toast value XXX
Bug ERROR: missing chunk number 0 for toast value.
The bug #18351 was previously reported in /messages/by-id/18351-f6e06364b3a2e669@postgresql.org but not resolved.
I have made reproducing easier, figured out the cause of the bug, and developed
a prototype patch, though it has known issues I'd like feedback on.
Reproduction
============
Tested on PostgreSQL 17.10
1) Terminal 1:
psql -d postgres
2) Terminal 1:
DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl (i int, t text);
CREATE INDEX ON tbl (i);
ALTER TABLE tbl ALTER COLUMN t SET STORAGE EXTERNAL;
INSERT INTO tbl(i, t) VALUES (1, repeat('1234567890', 250));
3) Terminal 2:
psql -d postgres
4) Terminal 2:
BEGIN;
SELECT txid_current();
5) Terminal 3:
createdb d1
6) Terminal 3:
psql -d d1
7) Terminal 3:
BEGIN;
SELECT txid_current();
8) Terminal 1:
DELETE FROM tbl WHERE i = 1;
9) Attach gdb to the backend from terminal 1
10) Set breakpoint at vacuum_rel(toast_relid, NULL, &toast_vacuum_params,
bstrategy); (line 2300 in src/backend/commands/vacuum.c)
11) Terminal 1:
VACUUM(VERBOSE) tbl;
12) Wait for the breakpoint to be hit
13) Terminal 2:
COMMIT; (or just \q)
14) Detach the process in gdb
15) Terminal 1:
CREATE INDEX ON tbl(t);
This should produce: ERROR: missing chunk number 0 for toast value …
The bug stems from different horizons in VACUUM table and its TOAST.
Two key mechanisms are involved:
1) Horizon computation (ComputeXidHorizons, called by
GetOldestNonRemovableTransactionId): iterates over processes in the
procarray, but skips those with PROC_IN_VACUUM set, and only considers
processes in the same database selecting the minimum for the
data_oldest_nonremovable. This value also feeds into
GlobalVisState->definitely_needed (which can only grow).
2) Snapshot computation (GetSnapshotData): also skips PROC_IN_VACUUM
processes, but does NOT filter by database — transactions in all databases
contribute to the snapshot's xmin.
During the main table's VACUUM, a transaction in the same database holds the
data horizon up, so the tuple survives (it is RECENTLY_DEAD) — but by the
time we vacuum the TOAST table, that transaction has committed. The TOAST
tuples get removed, because with no other active processes in this database
OldestXmin become max computed (that is >xmax).
Later, CREATE INDEX on the main table computes its own OldestXmin. Our process
is no longer in VACUUM, so its xmin (carried over from the snapshot - minimum
txid obtained from backend in another database) is now considered. This xmin
is less than the dead tuple's xmax, so HeapTupleSatisfiesVacuum classifies
it as RECENTLY_DEAD rather than DEAD. CREATE INDEX tries to fetch the TOAST
value — but it's already gone.
Prototype patch
===============
The core idea: when vacuuming a TOAST table, reuse the OldestXmin that was
computed for the parent table, rather than computing a fresh one that
may have advanced past it.
The prototype patch adds two fields to VacuumParams:
- cached_parent_oldest_xmin: stores the OldestXmin from the parent table
- cached_parent_cutoffs_valid: indicates the cached value is usable
In heap_vacuum_rel(), if we're vacuuming a TOAST table and the parent's
OldestXmin is available, we use it instead of calling
GetOldestNonRemovableTransactionId() again. This prevents the TOAST vacuum
from removing tuples (based on OldestXmin and definitely_needed) that the
main table's vacuum considered still visible.
Known issues
============
Make check fails. One of the problems is cutoff for removing and freezing
tuples is far in the past. This causes assertion failures and incorrect
freezing behavior.
Alternative approach
====================
An alternative would be to add a definitely_needed check alongside
OldestXmin in create index, so that a tuple classified as RECENTLY_DEAD
by OldestXmin would be reclassified as DEAD if definitely_needed says it's safe
to remove. However, this adds an extra visibility check during CREATE INDEX,
which could cause a performance regression.
I'd appreciate comments on whether the "cache parent OldestXmin for TOAST
vacuum" approach worth pursuing, despite the freezing complications?
Or is there a cleaner way?
Best regards,
Ekaterina Testova, Postgres Professional
Attachments:
caching_oldest_xmin.patchtext/x-diff; name="=?UTF-8?B?Y2FjaGluZ19vbGRlc3RfeG1pbi5wYXRjaA==?="Download+29-3
On Tue, 19 May 2026 at 23:05, Тестова Екатерина
<e.testova@postgrespro.ru> wrote:
Bug ERROR: missing chunk number 0 for toast value.
The bug #18351 was previously reported in /messages/by-id/18351-f6e06364b3a2e669@postgresql.org but not resolved.
I have made reproducing easier, figured out the cause of the bug, and developed
a prototype patch, though it has known issues I'd like feedback on.
[ summary of the problem; toast table cleanup racing with index
creation & vacuum full that needs access to RECENTLY_DEAD tuples'
toast data ]
Prototype patch
===============
The core idea: when vacuuming a TOAST table, reuse the OldestXmin that was
computed for the parent table, rather than computing a fresh one that
may have advanced past it.
I think this is insufficient; heap on-access pruning and btree's
aggressive index tuple removal (when the page would split) will still
cause some cleanup of data that could still be considered
RECENTLY_DEAD by a possible concurrent maintenance session (CREATE
INDEX, REINDEX, VACUUM FULL, ...).
I suspect the only possible approach here is to be extra careful when
detoasting attributes of RECENTLY_DEAD tuples, and just skip
RECENTLY_DEAD tuples if they are missing any parts of their externally
toasted attributes.
We *can* reduce the likelyhood of this issue by regularly updating the
OldestXmin used for HeapTupleSatisfiesVacuum() with more recent
GetOldestNonRemovableTransactionId(heapRelation) in e.g.
heapam_index_build_range_scan, but I can guarantee that it won't be a
universal fix for the issue: We still determine the liveness (well,
scan inclusion) of the heap tuple ahead of reading the toast data, and
in the time between those two page accesses there is more than enough
time for a concurrent session to come in and prune the toast page that
holds the equally RECENTLY_DEAD toast tuples, or remove the items from
the btree with bottom-up index removal.
So, missing toast data is (sadly) to be expected when working with
RECENTLY_DEAD tuples. We'll "just" have to find a way to avoid
completely erroring out in those contexts.
Alternative approach
====================
An alternative would be to add a definitely_needed check alongside
OldestXmin in create index, so that a tuple classified as RECENTLY_DEAD
by OldestXmin would be reclassified as DEAD if definitely_needed says it's safe
to remove. However, this adds an extra visibility check during CREATE INDEX,
which could cause a performance regression.
OldestXmin is already populated by (what should be) the value of the
relevant GlobalVisState.definitely_needed; it's just never updated
during an index build. Adding additional checks won't help any more
than just updating the OldestXmin horizon used.
I'd appreciate comments on whether the "cache parent OldestXmin for TOAST
vacuum" approach worth pursuing, despite the freezing complications?
Or is there a cleaner way?
I don't think either approach is sufficient to fix the bug; see my
inline comments about the issues. I think the simplest way to reduce
the frequency of the INDEX issue is to regularly update OldestXmin,
but to completely fix the bug we'll probably have to start
materializing relevant toasted attributes more aggressively before
passing the tuple to the IndexBuildCallback. Any TOAST-related
visibility errors can then be ignored, rather than breaking the index
build state due to unexpected errors when materializing the data.
Kind regards,
Matthias van de Meent
Databricks (https://www.databricks.com)
On Mon, Jul 06, 2026 at 05:44:50PM +0200, Matthias van de Meent wrote:
I think this is insufficient; heap on-access pruning and btree's
aggressive index tuple removal (when the page would split) will still
cause some cleanup of data that could still be considered
RECENTLY_DEAD by a possible concurrent maintenance session (CREATE
INDEX, REINDEX, VACUUM FULL, ...).
Reducing the likelihood of the problem won't address its root. While
I looked into all that yesterday, I have looked at an approach based
on a more aggressive detoasting for index builds, and it's pretty
promising:
/messages/by-id/akyM9G67ZIhPXjmQ@paquier.xyz
The patch includes tests that emulate the report of this thread. The
index build path should be able to live with the aggressive detoasting
(we need them anyway for the index build).
The CLUSTER/VACUUM_FULL/REPACK cases need a deeper chirurgy, as tuple
rewrites for the copy_for_cluster() case may not need detoasting at
all (case of tuples copied by reference, which is quite a common case
when UPDATEs don't touch the external TOAST blobs, just other
attributes). At least that's the assumptions the patch relies on.
Please note that rather than re-creating a new CF entry, I've attached
the other thread to your entry. It would be confusing to have two
entries for the same problem.
--
Michael
Matthias, thank you for the review and suggestions.
On Mon, Jul 06, 2026 at 05:44:50PM, Matthias van de Meent wrote:
I think this is insufficient; heap on-access pruning and btree's aggressive index tuple removal (when the page would split) will > still cause some cleanup of data that could still be considered RECENTLY_DEAD by a possible concurrent maintenance > session (CREATE INDEX, REINDEX, VACUUM FULL, ...).
That's a good point. I agree that the bug is not limited to removing toast tuples only during VACUUM.
We can reduce the likelyhood of this issue by regularly updating the OldestXmin used for HeapTupleSatisfiesVacuum() with > more recent GetOldestNonRemovableTransactionId(heapRelation) in e.g. heapam_index_build_range_scan
At the same time, getting the current OldestXmin more often will definitely not protect (and it won't particularly reduce the likelihood of) against the situation that I have cited. After all, there is an overestimation of definitely_needed in the vacuum interval.
I suspect the only possible approach here is to be extra careful when detoasting attributes of RECENTLY_DEAD tuples, and > just skip RECENTLY_DEAD tuples if they are missing any parts of their externally toasted attributes.
I've also been thinking about the approach of handling missing TOAST data without stopping the index scan. However, I have concerns about this approach.
The "missing chunk number" error can indicate actual data corruption, not just a race between heap and TOAST cleanup. We risk masking genuine storage corruption that should be surfaced to the user.
I will try to implement patch with careful handling the error of missing data from the visibility race between toast and the main table error in the near future. Based on your experience, can you tell me what I should pay attention to first of all when implementing?
Best regards,
Ekaterina Testova, Postgres Professional
Thank you for paying attention to the thread on the same topic. I will
definitely look at this thread and the proposed patch in the near future.
Best regards,
Ekaterina Testova, Postgres Professional
On 8 Jul 2026, at 12:43, Тестова Екатерина <e.testova@postgrespro.ru> wrote:
Thank you for paying attention to the thread on the same topic. I will
definitely look at this thread and the proposed patch in the near future.
Hi all,
Thanks for digging into this one. We care about it from an operational
angle, and I'd like to add a reproducer that might make it easier to work on.
The "missing chunk number" error can indicate actual data corruption,
not just a race between heap and TOAST cleanup. We risk masking genuine
storage corruption that should be surfaced to the user.
Yeah, that's why SQLSTATE XX001 (ERRCODE_DATA_CORRUPTED) matters to us. Our
fleet-wide corruption monitoring keys off that error class, so every occurrence
of this race pages me and a team as if it were on-disk corruption. We've
had to add this specific message to an allowlist to stop the noise; our allowlist
currently has just two entries, and I would be very happy to get back to an
empty one.
For completeness, the other entry is
"ERROR: requested WAL segment ... has already been removed"
raised as SQLSTATE 58P01 (undefined_file). I only mention it to
explain why accurate SQLSTATEs matter to us in practice. The TOAST case is
the on-topic one here.
To make iterating on a fix convenient, the attached patch turns the manual
gdb recipe from the first message into a deterministic TAP test. It adds a
"vacuum-before-toast" injection point in vacuum_rel().
The test asserts the correct behavior (CREATE INDEX succeeds), so it fails
on today's codebase.
Best regards, Andrey Borodin.
Attachments:
0001-Add-injection-point-test-for-TOAST-heap-vacuum-horiz.patchapplication/octet-stream; name=0001-Add-injection-point-test-for-TOAST-heap-vacuum-horiz.patch; x-unix-mode=0644Download+110-1
On Tue, Jul 14, 2026 at 02:45:11PM +0500, Andrey Borodin wrote:
To make iterating on a fix convenient, the attached patch turns the manual
gdb recipe from the first message into a deterministic TAP test. It adds a
"vacuum-before-toast" injection point in vacuum_rel().The test asserts the correct behavior (CREATE INDEX succeeds), so it fails
on today's codebase.
Is there any difference in coverage with the tests posted at [1]/messages/by-id/19519-fe02d8ff679d834d@postgresql.org? As
far as I can see, this is just a more complicated implementation of
the same thing. You are providing the same coverage for CREATE INDEX
and index builds, missing the other code paths with data copy.
I'd suggest to move the discussion entirely to the other thread, at
this stage, where the patch has been posted, even if I kind of agree
that the thread being marked as related only to REPACK can be
confusing.
The CF entry at [2]https://commitfest.postgresql.org/patch/6824/ -- Michael tracks both threads.
[1]: /messages/by-id/19519-fe02d8ff679d834d@postgresql.org
[2]: https://commitfest.postgresql.org/patch/6824/ -- Michael
--
Michael