Обсуждение: [ADMIN] Help: unfixable/undead error - missing chunk number 0 for toastvalue 413511 in pg_toast_25477

Поиск
Список
Период
Сортировка

[ADMIN] Help: unfixable/undead error - missing chunk number 0 for toastvalue 413511 in pg_toast_25477

От
Maris Jansons
Дата:
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.
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@lailio.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

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.
Hello,
> A couple things you can try.
> 1. reindex your toast table.
> 2. write a loop that ..

I already have done both, did not help, check the original message for details.

Best Regards,
Maris.

I have run this with success several times.  I modified it to be intuitive. There may be a typo or two but have a look at this:

DO $f$

DECLARE

    baddata TEXT;

    badid INT;

BEGIN

FOR badid IN SELECT id FROM badtable LOOP

    BEGIN

        SELECT badcolumn

        INTO columndata

        FROM badtable where id = badid;

    EXCEPTION

        WHEN OTHERS THEN

            RAISE NOTICE 'Data for ID % is corrupt', badid;

            CONTINUE;

    END;

END LOOP;

END;

$f$




On Wed, Jan 25, 2017 at 1:30 PM, Maris Jansons <maris@lailio.net> wrote:
Hello,
> A couple things you can try.
> 1. reindex your toast table.
> 2. write a loop that ..

I already have done both, did not help, check the original message for details.

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.
I have identified the problematic column, in my case it is api_response, my version of script:
DO $f$
DECLARE
    baddata TEXT;
    badid INT;
BEGIN
FOR badid IN SELECT id FROM api_logs LOOP
    BEGIN
        SELECT api_response
        INTO baddata
        FROM api_logs where id = badid;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Data for ID % is corrupt', badid;
            CONTINUE;
    END;
END LOOP;
END;
$f$
However this script finishes without errors, but if I run simple
> select api_response from api_logs;
I get:
ERROR:  missing chunk number 0 for toast value 413511 in pg_toast_25477


Maybe I have messed something up in the script?

Best Regards,
Maris


From: "Jorge Torralba" <jorge.torralba@gmail.com>
To: "Maris Jansons" <maris@lailio.net>
Cc: "pgsql-admin" <pgsql-admin@postgresql.org>
Sent: Wednesday, January 25, 2017 11:52:08 PM
Subject: Re: [ADMIN] Help: unfixable/undead error - missing chunk number 0 for toast value 413511 in pg_toast_25477

I have run this with success several times.  I modified it to be intuitive. There may be a typo or two but have a look at this:

DO $f$

DECLARE

    baddata TEXT;

    badid INT;

BEGIN

FOR badid IN SELECT id FROM badtable LOOP

    BEGIN

        SELECT badcolumn

        INTO columndata

        FROM badtable where id = badid;

    EXCEPTION

        WHEN OTHERS THEN

            RAISE NOTICE 'Data for ID % is corrupt', badid;

            CONTINUE;

    END;

END LOOP;

END;

$f$

Maris Jansons <maris@lailio.net> writes:
> I have identified the problematic column, in my case it is api_response, my version of script:
> DO $f$
> DECLARE
> baddata TEXT;
> badid INT;
> BEGIN
> FOR badid IN SELECT id FROM api_logs LOOP
> BEGIN
> SELECT api_response
> INTO baddata
> FROM api_logs where id = badid;
> EXCEPTION
> WHEN OTHERS THEN
> RAISE NOTICE 'Data for ID % is corrupt', badid;
> CONTINUE;
> END;
> END LOOP;
> END;
> $f$
> However this script finishes without errors, but if I run simple
>> select api_response from api_logs;
> I get:
> ERROR: missing chunk number 0 for toast value 413511 in pg_toast_25477

> Maybe I have messed something up in the script?

Oh ... what is happening, I think, is that the toast pointers are getting
stored into the "baddata" local variable as-is.  The problem would only
get detected if the system has to fetch the toasted value, and this code
doesn't make it do so.

You could do something that requires fetching the value, perhaps

SELECT api_response || '' INTO baddata ...

            regards, tom lane


Hello,
Most excellent!!!
Thank you both Jorge and Tom. The script worked correctly after Toms suggestion and gave me the correct id of the
brokenrecord. 
After removing this record from the table, reindexing both pg_toast and the broken table, then vacuuming it – table is
workingnow as expected. 
Now backups and selects work again.

Thank you and have a great day,
Maris.

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Maris Jansons" <maris@lailio.net>
Cc: "Jorge Torralba" <jorge.torralba@gmail.com>, "pgsql-admin" <pgsql-admin@postgresql.org>
Sent: Thursday, January 26, 2017 6:03:03 PM
Subject: Re: [ADMIN] Help: unfixable/undead error - missing chunk number 0 for toast value 413511 in pg_toast_25477

Maris Jansons <maris@lailio.net> writes:
> I have identified the problematic column, in my case it is api_response, my version of script:
> DO $f$
> DECLARE
> baddata TEXT;
> badid INT;
> BEGIN
> FOR badid IN SELECT id FROM api_logs LOOP
> BEGIN
> SELECT api_response
> INTO baddata
> FROM api_logs where id = badid;
> EXCEPTION
> WHEN OTHERS THEN
> RAISE NOTICE 'Data for ID % is corrupt', badid;
> CONTINUE;
> END;
> END LOOP;
> END;
> $f$
> However this script finishes without errors, but if I run simple
>> select api_response from api_logs;
> I get:
> ERROR: missing chunk number 0 for toast value 413511 in pg_toast_25477

> Maybe I have messed something up in the script?

Oh ... what is happening, I think, is that the toast pointers are getting
stored into the "baddata" local variable as-is.  The problem would only
get detected if the system has to fetch the toasted value, and this code
doesn't make it do so.

You could do something that requires fetching the value, perhaps

SELECT api_response || '' INTO baddata ...

            regards, tom lane