On Tue, Feb 21, 2012 at 3:32 PM, Tom Lane <t...@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? -- Maxim Boguk Senior Postgresql DBA.
