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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
