From: | "Ed L(dot)" <pgsql(at)bluepolka(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | lock query |
Date: | 2007-01-16 18:13:00 |
Message-ID: | 200701161113.01125.pgsql@bluepolka.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2007-01-16 18:18:09 | Re: Temp Table Within PLPGSQL Function - Something Awry |
Previous Message | Bruno Wolff III | 2007-01-16 18:11:58 | Re: Performance with very large tables |