Re: Backends stalled in 'startup' state: index corruption

Greg Sabino Mullane <greg@endpoint.com> writes:
> On Fri, May 25, 2012 at 07:02:42PM -0400, Tom Lane wrote:
>> pg_attribute just enough smaller to avoid the scenario.  Not sure about
>> Greg's case, but he should be able to tell us the size of pg_attribute
>> and his shared_buffers setting ...

> pg_attribute around 5 MB (+6MB indexes), shared_buffers 4GB. However, 
> there is a *lot* of churn in pg_attribute and pg_class, mostly due 
> to lots of temporary tables.

> P.S. Hmmm that's weird, I just double-checked the above and pg_attribute 
> is now 52MB/70MB (the original figures were from yesterday). At any rate, 
> nowhere near 1/4 shared buffers.

Hmph.  And Jeff swears his isn't large enough to trigger syncscans
either.  This could all be due to the "thundering herd" effect of a lot
of processes all doing the same only-moderately-expensive-in-itself
thing; except it's hard to see how the problem gets rolling unless the
single-process cache reload time is already a lot more than the
inter-arrival time.

Would you guys please try this in the problem databases:

select a.ctid, c.relname
from pg_attribute a join pg_class c on a.attrelid=c.oid
where c.relnamespace=11 and c.relkind in ('r','i')
order by 1 desc;

If you see any block numbers above about 20 then maybe the triggering
condition is a row relocation after all.
        regards, tom lane