Karsten Hilbert <Karsten.Hilbert@gmx.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, I think the easiest "fix" would be to manually
insert rows with the correct chunk_id and chunk_seq, and ideally
with chunk_data of the appropriate length. Then deletion of the
reference should work.
Unfortunately, it seems like you can't do that either, short of
hacking up the backend or writing some custom C code, because the
executor won't let you open a toast table as result relation :-(.
I wonder if we should change it to allow that when
allow_system_table_mods is true? This isn't the first time we've
seen people need to be able to do surgery on a toast table.
regards, tom lane