How to find the row corresponding to a given toast value?

From: David Boreham <david_list(at)boreham(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: How to find the row corresponding to a given toast value?
Date: 2009-10-19 20:57:36
Message-ID: 4ADCD2C0.3090105@boreham.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


I have a (large) corrupted 8.3.7 database that I'd like to fix.
It has this problem :

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: missing chunk number 2 for toast value 122220114 in pg_toast_16426

I've seen this particular syndrome before and fixed it by deleting the table row that refers
to the missing toast value. The table row was discovered by chance because the user to whom the
data belonged complained that his service wasn't working. In this current case that hasn't
happened, so I'm clueless as to which row I need to delete.

I've tried dumping the table to see if the records happen to be in primary key order
(hence the N+1'th record would be the bad one). Unfortunately this didn't help
because the records appear to be out of order in the dump.

Hence my question : is there an efficient way to determine which table row
references that missing toad value ?

My best option right now is to issue SELECT ... LIMIT .. OFFSET ... queries
to identify the row. This is likely to take a while though because there's
tens of GBytes in the table, and the database is quite heavily loaded.

Any better ideas are most welcome, thanks.

Browse pgsql-general by date

  From Date Subject
Next Message Christophe Pettus 2009-10-19 21:54:38 Mac OS Roman import?
Previous Message Peter Hunsberger 2009-10-19 20:52:19 Re: Free Tool to design Postgres Databases