Re: 8.4.0 data loss / HOT-related bug

2009/8/21 Radoslaw Zielinski <radek(at)pld-linux(dot)org>:
> Greg Stark <gsstark(at)mit(dot)edu> [2009-08-21 18:38]:
> [...]
>> the toast table. Not unless you can dump the whole index and find
>> pointers in there or can find the details in the wal log.
>
> You mean the primary key index?  How do I dump it?

Actually I mean the key for the toast table.

Let me ask firstly do you get anything if you just do select * from
pg_toast.pg_toast_29644 where chunk_id = 120741 ?

And secondly, what do you get if you do "select length(htmlblob) from
tab where id = ?" which should work even if the toast data is broken.

To dump the index -- this will be tedious, but:

Load the pageinspect contrib module and run this:
select (x).* from (select bt_page_items('t_pkey',1) as x ) as y;
select (x).* from (select bt_page_items('t_pkey',2) as x ) as y;
...

Until you get an error. Then look through the output for any pointers

The hex at the end is the index key which in the case of a toast table
starts with the chunk id, you're looking for 120741 which is 0001D7A5
in hex or A5D70100 in little-endian...

If you find any such rows send them and they might tell us what's
going on or where to look for the missing toast records to see what's
going on.

However I'm kind of skeptical that you'll find any. IIRC Btree marks
pointers that it finds point to dead records and reuses them as soon
as possible. Still, maybe nothing's been inserted on that page yet.

--
greg
http://mit.edu/~gsstark/resume.pdf