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

On 3/15/20 1:21 PM, Karsten Hilbert wrote:
> On Sun, Mar 15, 2020 at 12:58:53PM -0700, Adrian Klaver wrote:
>
>>>> We then tried to DELETE the offending row
>>>>
>>>> delete from blobs.doc_obj where pk = 82224;
>>>>
>>>> but that, again, shows the "unexpected chunk" problem.
>>>
>>> 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 ?
>>>
>>> I can't find documentation pointing to a fundamental
>>> implementation difference that suggests so.
>>
>> https://www.postgresql.org/docs/12/storage-toast.html#STORAGE-TOAST-ONDISK
>>
>> "During an UPDATE operation, values of unchanged fields are normally
>> preserved as-is; so an UPDATE of a row with out-of-line values incurs no
>> TOAST costs if none of the out-of-line values change."
>
> However, where is the fault in my thinking ?
>
> -> An UPDATE actually *would* change the TOASTed BYTEA field (which is corrupt).
>
> I had hoped that the DELETE would NOT have to touch the TOAST
> table at all (and thereby not check the chunks) as "all it
> needs to do" is mark the row in the *primary* table as
> not-needed-anymore.
>
> I must be misunderstanding something.

Except it would also need to delete the toast entries as well.
>
> Karsten
> --
> GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com