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
