Query < and >= 9.2 to reflect query vs current_query and pid vs procpid.
← Older revision
Revision as of 19:03, 29 July 2013
Line 40:
Line 40:
* Here's almost quite the same thing but with some more details:
* Here's almost quite the same thing but with some more details:
−
+
-- For PostgreSQL Version < 9.2
select bl.pid as blocked_pid, a.usename as blocked_user,
select bl.pid as blocked_pid, a.usename as blocked_user,
ka.current_query as blocking_statement, now() - ka.query_start as blocking_duration,
ka.current_query as blocking_statement, now() - ka.query_start as blocking_duration,
Line 53:
Line 53:
on bl.transactionid = kl.transactionid and bl.pid != kl.pid
on bl.transactionid = kl.transactionid and bl.pid != kl.pid
where not bl.granted;
where not bl.granted;
+
+
--For PostgreSQL >= 9.2
+
select bl.pid as blocked_pid, a.usename as blocked_user,
+
ka.query as blocking_statement, now() - ka.query_start as blocking_duration,
+
kl.pid as blocking_pid, ka.usename as blocking_user, a.query as blocked_statement,
+
now() - a.query_start as blocked_duration
+
from pg_catalog.pg_locks bl
+
join pg_catalog.pg_stat_activity a
+
on bl.pid = a.pid
+
join pg_catalog.pg_locks kl
+
join pg_catalog.pg_stat_activity ka
+
on kl.pid = ka.pid
+
on bl.transactionid = kl.transactionid and bl.pid != kl.pid
+
* If you suspect intermittent locks are causing problems only sometimes, but are having trouble catching them in one of these live views, setting the [http://www.postgresql.org/docs/current/static/runtime-config-logging.html#GUC-LOG-LOCK-WAITS log_lock_waits] and related [http://www.postgresql.org/docs/current/static/runtime-config-locks.html#GUC-DEADLOCK-TIMEOUT deadlock_timeout] parameters can be helpful. Then slow lock acquisition will appear in the database logs for later analysis.
* If you suspect intermittent locks are causing problems only sometimes, but are having trouble catching them in one of these live views, setting the [http://www.postgresql.org/docs/current/static/runtime-config-logging.html#GUC-LOG-LOCK-WAITS log_lock_waits] and related [http://www.postgresql.org/docs/current/static/runtime-config-locks.html#GUC-DEADLOCK-TIMEOUT deadlock_timeout] parameters can be helpful. Then slow lock acquisition will appear in the database logs for later analysis.