Обсуждение: [ADMIN] Help: unfixable/undead error - missing chunk number 0 for toastvalue 413511 in pg_toast_25477
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
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:1945I 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, etcThen 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.
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.
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,> 2. write a loop that ..> A couple things you can try.> 1. reindex your toast table.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.
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$
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
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