I wonder if anyone might help me generate a SQL query that peers
into pg_locks, pg_stat_activity, etc and tells in plain language
exactly *who* each backend is blocked *on* while awaiting
lock(s).
Here's what I'm looking at now:
SELECT date_trunc('second', now()) as now, a.client_addr as ip,
l.pid, l.locktype, l.mode, l.granted,
r.relname, l.page, l.tuple, l.transactionid,
a.query_start, a.current_query as sql
FROM pg_locks l LEFT OUTER JOIN pg_class r ON r.oid = l.relation
LEFT OUTER JOIN pg_stat_activity a ON l.pid =
a.procpid
ORDER BY a.query_start ASC;
For busy systems with hundreds of backends and hundreds of
queries per second, I find the output of this query very
difficult to quickly who is holding the key lock(s) on which
blocked backends wait. What would be really helpful is a query
that generated output along the lines of:
"Backend pid 123 is blocked awaiting pid 456 lock on 'sessions'
relation."
Perhaps this function already exists? If not, what is needed to
get there?
TIA,
Ed