Re: unexpected chunk number 2 (expected 0) for toast value ... in pg_toast_18536

On Sun, Mar 15, 2020 at 08:11:18PM -0400, Tom Lane wrote:

> Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> writes:
> >>> According to
> >>> http://www.databasesoup.com/2013/10/de-corrupting-toast-tables.html
> >>> an UPDATE of the row is recommended -- should that work
> >>> better than a DELETE ?
>
> > OK, got that. What I now don't understand is how the UPDATE
> > won't have to touch the TOAST table when the TOASTed value
> > *is* UPDATEd:
> > update blobs.doc_obj set data = '' where pk = the_faulty_row;
> > (data is the BYTEA column)
>
> It makes no sense to me either; I wonder if Josh's recipe ever
> really worked? But it's clearly not working now, and that's
> what I'd expect, because any mechanism for removing the busted
> toast reference is going to cause the system to try to mark
> the toast rows deleted.
>
> Since you reindexed the toast table and it still doesn't find
> the missing chunks,

The user has reported that gratuitious and repeated use of
REINDEX/VACUUM has eventually led to a consistent database.
That one row went missing but can be re-created.

Unfortunately, I neither have the original data for testing
(it is a medical record database and the client won't hand
out copies for obvious reasons) nor can I ascertain the exact
order of steps they eventually took.

For the record.

Thanks,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B