Re: Let's invent a function to report lock-wait-blocking PIDs

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 record
pg_blocking_pids(pid int, OUT blocker int, OUT how text) returns setof record

Thanks,
nm

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