pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp: unfixable/undead error - missing chunk number 0 for toast value 413511 in pg_toast_25477
7+ messages / 3 participants
[nested] [flat]
* Help: unfixable/undead error - missing chunk number 0 for toast value 413511 in pg_toast_25477
@ 2017-01-25 14:53 Maris Jansons <maris@lailio.net>
0 siblings, 1 reply; 7+ messages in thread
From: Maris Jansons @ 2017-01-25 14:53 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help: unfixable/undead error - missing chunk number 0 for toast value 413511 in pg_toast_25477
@ 2017-01-25 16:50 Jorge Torralba <jorge.torralba@gmail.com>
parent: Maris Jansons <maris@lailio.net>
0 siblings, 1 reply; 7+ messages in thread
From: Jorge Torralba @ 2017-01-25 16:50 UTC (permalink / raw)
To: Maris Jansons <maris@lailio.net>; +Cc: pgsql-admin
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
>
> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help: unfixable/undead error - missing chunk number 0 for toast value 413511 in pg_toast_25477
@ 2017-01-25 21:30 Maris Jansons <maris@lailio.net>
parent: Jorge Torralba <jorge.torralba@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Maris Jansons @ 2017-01-25 21:30 UTC (permalink / raw)
To: Jorge Torralba <jorge.torralba@gmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help: unfixable/undead error - missing chunk number 0 for toast value 413511 in pg_toast_25477
@ 2017-01-25 21:52 Jorge Torralba <jorge.torralba@gmail.com>
parent: Maris Jansons <maris@lailio.net>
0 siblings, 1 reply; 7+ messages in thread
From: Jorge Torralba @ 2017-01-25 21:52 UTC (permalink / raw)
To: Maris Jansons <maris@lailio.net>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help: unfixable/undead error - missing chunk number 0 for toast value 413511 in pg_toast_25477
@ 2017-01-26 09:44 Maris Jansons <maris@lailio.net>
parent: Jorge Torralba <jorge.torralba@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Maris Jansons @ 2017-01-26 09:44 UTC (permalink / raw)
To: Jorge Torralba <jorge.torralba@gmail.com>; +Cc: pgsql-admin
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$
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help: unfixable/undead error - missing chunk number 0 for toast value 413511 in pg_toast_25477
@ 2017-01-26 16:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Maris Jansons <maris@lailio.net>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2017-01-26 16:03 UTC (permalink / raw)
To: Maris Jansons <maris@lailio.net>; +Cc: Jorge Torralba <jorge.torralba@gmail.com>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help: unfixable/undead error - missing chunk number 0 for toast value 413511 in pg_toast_25477
@ 2017-01-27 08:44 Maris Jansons <maris@lailio.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Maris Jansons @ 2017-01-27 08:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jorge Torralba <jorge.torralba@gmail.com>; pgsql-admin
Hello,
Most excellent!!!
Thank you both Jorge and Tom. The script worked correctly after Toms suggestion and gave me the correct id of the broken record.
After removing this record from the table, reindexing both pg_toast and the broken table, then vacuuming it – table is working now 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2017-01-27 08:44 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-01-25 14:53 Help: unfixable/undead error - missing chunk number 0 for toast value 413511 in pg_toast_25477 Maris Jansons <maris@lailio.net>
2017-01-25 16:50 ` Jorge Torralba <jorge.torralba@gmail.com>
2017-01-25 21:30 ` Maris Jansons <maris@lailio.net>
2017-01-25 21:52 ` Jorge Torralba <jorge.torralba@gmail.com>
2017-01-26 09:44 ` Maris Jansons <maris@lailio.net>
2017-01-26 16:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-01-27 08:44 ` Maris Jansons <maris@lailio.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox