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

On Sun, Mar 15, 2020 at 02:35:39PM -0700, Adrian Klaver wrote:

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

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)

Slightly confused :-)
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B