Re: Unfamous 'could not read block ... in file "...": read only 0 of 8192 bytes' again

Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> writes:
> On Tue, Feb 21, 2012 at 1:46 PM, Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> wrote:
>> There isn't possibility but close to 100% new inserted values were matched
>> a previously-existing primary
>> key value.
>> The table is hand-made 'materialyzed view'-type statistic table which is
>> getting recalculated via cron.

> To be clear - the new inserted values do match a previously-existing
> primary key values almost always.

OK, so that pretty much explains where the visible symptoms are coming
from: somehow, the table got truncated but its pkey index did not get
cleared out. So an insert creates an empty page zero, inserts a heap
tuple there, tries to insert an index entry. The btree code sees there
is an index entry for that key already, and tries to fetch the heap
tuple for that index entry to see if it's dead (which would allow the
insertion to proceed). But the block number the index is pointing at
isn't there, so you get the quoted error message. The insertion rolls
back, leaving a dead tuple that can be garbage-collected by autovacuum,
after which it truncates the table again --- but of course without
removing any index entries, except maybe one for TID (0,1) if that's
still there. Lather rinse repeat.

But this brings us little closer to understanding the cause of the
problem. How could the table have gotten truncated without vacuuming
out the index?

Are you sure the only operations happening on that table are INSERT,
DELETE, autovacuum? No DDL of any kind?

regards, tom lane