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

On Tue, Feb 21, 2012 at 3:32 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

There is some funny results:

hh=# VACUUM verbose agency_statistics_old;
INFO:  vacuuming "public.agency_statistics_old"
INFO:  index "agency_statistics_pkey" now contains 0 row versions in 605 pages
DETAIL:  0 index row versions were removed.
595 index pages have been deleted, 595 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  "agency_statistics_old": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
VACUUM

However when I try populate that table with production data I get an error:

hh=# insert into agency_statistics_old select * from agency_statistics;
ERROR:  could not read block 228 in file "base/16404/118881486": read only 0 of 8192 bytes

E.g. the database see that index have zero rows, but an insert still fail.

May be I should use pageinspect addon to see an actual index pages content?