Re: Unfamous 'could not read block ... in file "...": read only 0 of 8192 bytes' again - Mailing list pgsql-general

I wrote:
> 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.

Hmm ... actually there is a point that this theory doesn't explain
entirely.  If the probability of a collision with an existing index
entry was near 100%, then each hourly cron job should only have been
able to insert one or a few heap tuples before failing.  That would
not trigger an autovacuum right away.  Eventually the number of dead
tuples would build up to the point where autovacuum got interested,
but it strains credulity a bit to assume that this happened exactly
after the last hourly run before you renamed the table.  Yet, if that
didn't happen just that way, how come the size of the table is exactly
zero now?

The theory would be more satisfactory if we could expect that an hourly
run would be able to insert some thousands of tuples before failing,
enough to trigger an autovacuum run.  So I'm wondering if maybe the
index is *partially* cleaned out, but not completely.  Does this
materialized view have a fairly predictable number of rows, and if so
how does that compare to the number of entries in the index?  (If you
have no other way to get the number of entries in the index, try
inserting a dummy row, deleting it, and then VACUUM VERBOSE.)

                       regards, tom lane