Обсуждение: Fixing up a corrupted toast table
I have an older 7.4 installation which is giving this error during pg_dump: pg_dump: ERROR: missing chunk number 0 for toast value 25923965 pg_dump: SQL command to dump the contents of table "element" failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: missing chunk number 0 for toast value 25923965 (slightly edited, but not in any significant way). I believe that it's a known issue that occasionally causes this (this is not a bug report, and I'm quite aware of point upgrades and so on) and the server will likely be updated to 8.1.current within the month. 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? Cheers, Steve
Steve Atkins <steve@blighty.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
On Feb 17, 2006, at 6:29 AM, Tom Lane wrote: > Steve Atkins <steve@blighty.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
On Feb 17, 2006, at 8:27 AM, Steve Atkins wrote: > > On Feb 17, 2006, at 6:29 AM, Tom Lane wrote: > >> Steve Atkins <steve@blighty.com> writes: >>> pg_dump: ERROR: missing chunk number 0 for toast value 25923965 [snip] >> >> 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! Unfortunately, postgresql is smarter than I am. Any attempt to touch the toast table gives me: ERROR: cannot change TOAST relation "pg_toast_17410" 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? Cheers, Steve
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?
> 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 ...
regards, tom lane
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
On 3/9/06, Tom Lane <tgl@sss.pgh.pa.us> 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? +1 on allowing superusers to do that. I recently needed it. Especially cool would be if i could simply insert untoasted values there, so i can put some fake values there and detect them later. (As I cannot query 'what table row has toast_oid') -- marko