Re: Fixing up a corrupted toast table

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

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

regards, tom lane