>> What's odd about this is that the resulting "lock pileup" takes a
>> mysterious 2-3.5 seconds to clear, despite the fact that none of the
>> connections are *doing* anything during that time, nor are there
>> deadlock errors. In theory at least, the locks should clear out in
>> reverse order in less than a second; none of the individual statements
>> takes more than 10ms to execute.
Ok, I've collected more data. Looks like the case I was examining was
idiosyncratic; most of these lock pile-ups involve 400 or more locks
waiting held by around 20 different backends. Given this, taking 3
seconds to sort that all out doesn't seem that unreasonable.
Presumably there's a poll cycle of some sort for waiting statements?
Anyway, the obvious answer is for the user to fix their application.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com