Re: Help: unfixable/undead error - missing chunk number 0 for toast value 413511 in pg_toast_25477

A couple things you can try.

1. reindex your toast table.

2. write a loop that fetches every row in the table and capture with
exception the rows that it fails on. Then delete those rows. afterwards do
a dump of the table to /dev/null and see if it completes without errors. If
so, you will need to dump and recreate the table.

JT

On Wed, Jan 25, 2017 at 6:53 AM, Maris Jansons <maris(at)lailio(dot)net> wrote:

> Hello,
> I have run into a case on one of mine psql servers (9.5.3 on Ubuntu
> 14.04.4) where one table in db seems to be corrupted.
> Doing select on this table yields this result:
>
> select * from api_logs;
> ERROR: XX000: missing chunk number 0 for toast value 413511 in
> pg_toast_25477
> LOCATION: toast_fetch_datum, tuptoaster.c:1945
>
> I have tried to fix this by following this tutorial:
> https://newbiedba.wordpress.com/2015/07/07/postgresql-
> missing-chunk-0-for-toast-value-in-pg_toast/
> Reindexing both tables (
>
> REINDEX table pg_toast.pg_toast_25477;
> REINDEX table api_logs;
>
> ) does not help, VACUUM also gives the same result.
>
> Then I tried finding the bad values with the script:
>
> for ((i=0; i<"Number_of_rows_in_nodes"; i++ ));....
>
> This yielded following offset numbers (9,26838...), as tutorial showed, I
> did: select id from api_logs offset 9, etc
> Then I tried to select those records with thees ids, the weird thing is
> that select on those records worked.
> I then did COPY for these "supposedly broken" ids to csv, deleted them
> from table and tried VACUUM, which did not help at all.
> I also tried to delete offset 8, but this also did not help.
>
> How can I fix this table, what am I doing wrong? Somehow I think the ids
> of records at those offsets are not the correct ones that are really
> broken...
>
> Best regards,
> Maris.
>

--
Thanks,

Jorge Torralba
----------------------------

Note: This communication may contain privileged or other confidential
information. If you are not the intended recipient, please do not print,
copy, retransmit, disseminate or otherwise use the information. Please
indicate to the sender that you have received this email in error and
delete the copy you received. Thank You.