MIME-Version: 1.0
References: <1779078055.630771314@fmail2.qdit>
In-Reply-To: <1779078055.630771314@fmail2.qdit>
From: Matthias van de Meent <boekewurm+postgres@gmail.com>
Date: Mon, 6 Jul 2026 17:44:50 +0200
Message-ID: 
 <CAEze2WgRUfoR=tSQQm8zexVNeBbEYi9ZSPztrK63ObyfPxKpBw@mail.gmail.com>
Subject: Re: VACUUM FULL or CREATE INDEX fails with error: missing chunk
 number 0 for toast value XXX
To: =?UTF-8?B?0KLQtdGB0YLQvtCy0LAg0JXQutCw0YLQtdGA0LjQvdCw?=
 <e.testova@postgrespro.ru>
Cc: pgsql-hackers@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAEze2WgRUfoR%3DtSQQm8zexVNeBbEYi9ZSPztrK63ObyfPxKpBw%40mail.gmail.com>
Precedence: bulk

On Tue, 19 May 2026 at 23:05, =D0=A2=D0=B5=D1=81=D1=82=D0=BE=D0=B2=D0=B0 =
=D0=95=D0=BA=D0=B0=D1=82=D0=B5=D1=80=D0=B8=D0=BD=D0=B0
<e.testova@postgrespro.ru> wrote:
>
> Bug ERROR: missing chunk number 0 for toast value.
> The bug #18351 was previously reported in https://www.postgresql.org/mess=
age-id/flat/18351-f6e06364b3a2e669%40postgresql.org but not resolved.
> I have made reproducing easier, figured out the cause of the bug, and dev=
eloped
> 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
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> The core idea: when vacuuming a TOAST table, reuse the OldestXmin that wa=
s
> 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
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> 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 IND=
EX,
> 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)