Re: missing chunk number 0 for toast value 25693266 in pg_toast_25497233

On Fri, May 7, 2010 at 01:24, Bryan Murphy <bmurphy1976(at)gmail(dot)com> wrote:
> I'm running into this issue again:
>
> psql --version
> psql (PostgreSQL) 8.3.7
>
> COPY items_extended TO '/dev/null';
> ERROR:  missing chunk number 0 for toast value 25693266 in pg_toast_25497233
>
> Unfortunately, I do not know where these are coming from and I cannot
> replicate the data in at least one of my tables (which has 20 million
> records) because of this.  I've already found 10 bad records.  There
> are more.
>
> I have four tables with 20 million records (and a fifth which has even
> more) that may have this problem.  Right now, checking every record
> one by one is going to take *DAYS* to complete, and I'm in the middle
> of adding additional nodes to our cluster because we are already
> stretched to the limit.
>
> I have a few options, such as trying to check batches of records and
> spinning up multiple checkers in parallel looking at different subsets
> of the data on wal shipped spares (assuming the wal shipped spares
> would suffer the same problem, which is a big assumption), but this is
> a lot of effort to get going.

Try doing a binary search with LIMIT. E.g., if you have 20M reecords,
do a SELECT * FROM ... LIMIT 10M. (throw away the results) If that
broke, check the upper half, if not, check the lower one (with
OFFSET).

If you have a serial primary key or something, you can use WHERE on it
which will likely be a lot faster than using LIMIT, but the same idea
applies - do a binary search. Should take a lot less than days, and is
reasonably easy to script.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/