Re: Fixing up a corrupted toast table

On Mar 8, 2006, at 3:07 PM, Tom Lane wrote:

> Steve Atkins <steve@blighty.com> writes:
>>>> make it happy by inserting a dummy row into the toast table
>>>> (chunk ID
>>>> as specified in the error, chunk sequence 0, any old data value).
>
>> Any attempt to touch the toast table gives me:
>> ERROR:  cannot change TOAST relation "pg_toast_17410"
>
> Ugh.  Maybe we should allow superusers to do that?  Or is it too
> much of
> a foot-gun?

It turns out that you don't need to do this to delete bad rows once
you've found the ctid, so it's not relevant here.

>> If I set relkind to 'r' for the toast table, shove some fake data
>> in there and set it back to 't' that should do it, shouldn't it?
>
> Offhand I think this would work, but suggest trying it in a scratch
> database first ...

Seems to work. I'm just using it to replicate the damage in a test
database.

(For the archives - I have a perl script to find the ctid of damaged
rows reliably and remove them that works on the test database.
We'll see if it works in production.)

Cheers,
   Steve