SEO

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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster