Re: Fixing up a corrupted toast table

On Feb 17, 2006, at 6:29 AM, Tom Lane wrote:

> Steve Atkins <steve(at)blighty(dot)com> writes:
>> pg_dump: ERROR: missing chunk number 0 for toast value 25923965
>
>> I'd like to make the current problem go away, though, perhaps by
>> deleting the relevant row in the element table. I'm not quite sure
>> how
>> to go about that, though. Could anyone point me in the right
>> direction?
>
> First thing you should try is REINDEXing the toast table. (I think in
> 7.4, reindexing the owning table will do this too; try that if reindex
> won't let you hit the toast table directly.)

Yes, forgot to mention I'd already tried that. Sorry.

>
> If that doesn't work, the standard technique for locating damaged data
> should help: find the bad row by identifying the largest N for which
> "SELECT * FROM table LIMIT n" doesn't fail, then "SELECT ctid FROM
> table
> OFFSET n LIMIT 1". You may be able to delete the bad row with "DELETE
> FROM table WHERE ctid = 'value gotten above'", but I wouldn't be too
> surprised if the DELETE gives the same error. If so, you can probably
> 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).

OK, that's what I was looking for. Thanks!

Cheers,
Steve