Re: list blocking queries

Scot Kreienkamp <SKreien(at)la-z-boy(dot)com> writes:
> My apologies for the slightly novice post on this, but I'm a bit stumped. I have this query that I found on the net and adapted a little to find the queries that were blocking:

> "select bl.pid as \"Blocked PID\", a.usename as \"Blocked User\", kl.pid as \"Blocking PID\", ka.usename as \"Blocking User\", to_char(age(now(), a.query_start),'HH24h:MIm:SSs') as \"Age\" from pg_catalog.pg_locks bl join pg_catalog.pg_stat_activity a on bl.pid = a.procpid join pg_catalog.pg_locks kl join pg_catalog.pg_stat_activity ka on kl.pid = ka.procpid on bl.transactionid = kl.transactionid and bl.pid != kl.pid where not bl.granted;"

Hm, that would only have worked for rather small values of "work",
because it's matching pg_locks entries on the basis of the transactionid
field, which means it will only detect conflicts for locks on
transaction IDs. There are a lot of other types of locks. You need
something more like

join ... on bl.locktype = kl.locktype
and bl.database is not distinct from kl.database
and bl.relation is not distinct from kl.relation
and bl.page is not distinct from kl.page
and bl.tuple is not distinct from kl.tuple
and bl.virtualxid is not distinct from kl.virtualxid
and bl.transactionid is not distinct from kl.transactionid
and bl.classid is not distinct from kl.classid
and bl.objid is not distinct from kl.objid
and bl.objsubid is not distinct from kl.objsubid
and bl.pid != kl.pid

Since most of these fields will be nulls in any specific rows, you have
to use "is not distinct from" not just "=". Tedious, I know.

The WHERE clause seems a few bricks shy of a load as well; you need

where kl.granted and not bl.granted

if you don't want it to claim that fellow blockees are blocking each
other. (In some cases that would actually be a fair statement, but
I don't think it's possible to tell from pg_locks who's queued behind
whom in the wait-list for a lock, so it's probably best not to try
to show those relationships.)

regards, tom lane