Thread: pg_locks doesn't check for interrupts?
Hackers, Since querying pg_locks can be intrusive due to needing to lock the lock partitions, when I'm collecting data about locks I generally put a statement_timeout on it. However, I'm noticing that this statement_timeout appears to be completely ignored; I've seen this query run for up to 10 minutes* when the database is heavily loaded. This it seems likely to me that the functions under pg_locks aren't checking for interrupts. Anybody checked this already? (yes, when a 64,000 item lock table is mostly full of locks, queries against pg_locks *can* take 10 minutes) -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Josh Berkus <josh@agliodbs.com> writes: > Since querying pg_locks can be intrusive due to needing to lock the lock > partitions, when I'm collecting data about locks I generally put a > statement_timeout on it. However, I'm noticing that this > statement_timeout appears to be completely ignored; I've seen this query > run for up to 10 minutes* when the database is heavily loaded. This it > seems likely to me that the functions under pg_locks aren't checking for > interrupts. Anybody checked this already? Whether they do or not, I don't think we allow CHECK_FOR_INTERRUPTS to trigger while holding an LWLock. So this would not be a trivial thing to fix. regards, tom lane
On 11/18/2014 10:47 AM, Tom Lane wrote: > Josh Berkus <josh@agliodbs.com> writes: >> Since querying pg_locks can be intrusive due to needing to lock the lock >> partitions, when I'm collecting data about locks I generally put a >> statement_timeout on it. However, I'm noticing that this >> statement_timeout appears to be completely ignored; I've seen this query >> run for up to 10 minutes* when the database is heavily loaded. This it >> seems likely to me that the functions under pg_locks aren't checking for >> interrupts. Anybody checked this already? > > Whether they do or not, I don't think we allow CHECK_FOR_INTERRUPTS to > trigger while holding an LWLock. So this would not be a trivial thing > to fix. Hmm. So the basic problem is that querying pg_locks itself can make an already bad locking situation worse (I've seen it contribute to a total lockup, which didn't resolve until I terminated the query against pg_locks). I don't see a clear way to make it less dangerous, so I was hoping that at least making it time out made it safer to use. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com