Ruizs Place

If you found this through a search, you probably have seen something like this in your logs:
ERROR: missing chunk number 0 for toast value 87246446

Searching for this issue on the net, I found that this is usually caused by a corrupt row in the database. The solution is to find the corrupt row, and delete it (and/or update it with proper, non-corrupt values).

Unfortunately, there is no easy way to find the row that is giving you grief (or even the table if you don’t know what is causing this error). You basically have to narrow down your search by looking at parts of the database. If you have some sort of date or timestamp column in your table, you can narrow it down a lot more easily, if you know approximately when the error started:


mydb=# select id from mytable where date_created > timestamp '2009-02-27 00:00:00' and date_created < timestamp '2009-03-01 23:59:00';
id
--------------------------------------
3ab226cf-a972-463d-b5a1-148fe39672b5
10daca73-b2b3-470c-a258-5c92d21cfbb6
(2 rows)


Luckily it's only two rows, and the first one is the culprit:

mydb=# select * from mytable where id='3ab226cf-a972-463d-b5a1-148fe39672b5';
ERROR: missing chunk number 0 for toast value 87246446


Now, on to delete it:
mydb=# delete from mytable where id='3ab226cf-a972-463d-b5a1-148fe39672b5';

If you don’t have a date/timestamp column in your table, you could use LIMIT and OFFSET to narrow it down. I.e. a series of statements like (increasing the offset each time):

select * from mytable order by id limit 1000 offset 5000;


Tags: howto, mysql, postgresql, sysadmin