Re: Let's invent a function to report lock-wait-blocking PIDs - Mailing list pgsql-hackers

On Wed, Mar 20, 2013 at 02:02:32PM -0400, Tom Lane wrote:
[fun query for appraising lock contention]

> This is way more knowledge than we (should) want a client to embed about
> which lock types block which others.  What's worse, it's still wrong.
> The query will find cases where one of the test sessions *directly*
> blocks another one, but not cases where the blockage is indirect.
> For example, consider that A holds AccessShareLock, B is waiting for
> AccessExclusiveLock on the same object, and C is queued up behind B
> for another AccessShareLock.  This query will not think that C is
> blocked, not even if B is part of the set of sessions of interest
> (because B will show the lock as not granted); but especially so if
> B is not part of the set.
> 
> I think that such situations may not arise in the specific context that
> isolationtester says it's worried about, which is to disregard waits for
> locks held by autovacuum.  But in general, you can't reliably tell who's
> blocking whom with a query like this.

Indeed, isolationtester only uses the lock wait query when all but one session
is idle (typically idle-in-transaction).  But a more-general implementation of
the isolationtester concept would need the broader comprehension you describe.

> If isolationtester were the only market for this type of information,
> maybe it wouldn't be worth worrying about.  But I'm pretty sure that
> there are a *lot* of monitoring applications out there that are trying
> to extract who-blocks-whom information from pg_locks.

Agreed; such a feature would carry its own weight.  Unless the cost to
implement it is similar to the overall cost of just making the affected
timeout values high enough, I do think it's best delayed until 9.4.

> I propose that we should add a backend function that simplifies this
> type of query.  The API that comes to mind is (name subject to
> bikeshedding)
> 
>     pg_blocking_pids(pid int) returns int[]
> 
> defined to return NULL if the argument isn't the PID of any backend or
> that backend isn't waiting for a lock, and otherwise an array of the
> PIDs of the backends that are blocking it from getting the lock.
> I would compute the array as
> 
>     PIDs of backends already holding conflicting locks,
>     plus PIDs of backends requesting conflicting locks that are
>     ahead of this one in the lock's wait queue,
>     plus PIDs of backends that block the latter group of PIDs
>     (ie, are holding locks conflicting with their requests,
>     or are awaiting such locks and are ahead of them in the queue)
> 
> There would be some cases where this definition would be too expansive,
> ie we'd release the waiter after only some of the listed sessions had
> released their lock or request.  (That could happen for instance if we
> concluded we had to move up the waiter's request to escape a deadlock.)
> But I think that it's better to err in that direction than to
> underestimate the set of relevant PIDs.

That definition seems compatible with, albeit overkill for, the needs of
isolationtester.  However, I have an inkling that we should expose those
categories.  Perhaps one of these interfaces?
pg_blocking_pids(pid int, OUT blocker int, OUT waiting bool, OUT direct bool) returns setof recordpg_blocking_pids(pid
int,OUT blocker int, OUT how text) returns setof record
 

Thanks,
nm

-- 
Noah Misch
EnterpriseDB                                 http://www.enterprisedb.com