I was just helping out some Red Hat colleagues who were trying to deal
with a corrupted database in which pg_dump was failing like so:
pg_dump: Error message from server: ERROR: missing chunk number 3 for toast
value 205026
Frequently the best recovery action for this involves finding and
deleting the row that references the TOAST value ... but there is no
good way to find that row. AFAIK you have to engage in a very tedious
binary search to see where you get the error and where you don't. On
a large table this is just not fun at all; especially if you have or
think you may have multiple corrupted rows.
It strikes me that it'd be worth defining a function that could extract
the toast OID of any toasted datum, along the lines of
function pg_get_toast_id(anyelement) returns oid
When faced with such a problem, you could do
select ctid from my_giant_table
where pg_get_toast_id(a_toastable_column) = 205026
or pg_get_toast_id(another_toastable_column) = 205026
...;
This would involve a seqscan since there'd be no way to index it,
but at least it'd be only one seqscan rather than repeated trials.
I'd envision making the thing return NULL if given a varlena value
that happened not to be toasted out-of-line. If given a non-varlena
data type, it could either return NULL or raise an error, depending
on whether you think that represents pilot error or not.
Comments? Anyone else ever wished they had this?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
