PostgreSQL Lock Monitoring

pg>9.2

postgres=# SELECT Blocked_locks.pid as Blocked_pid,
postgres-# Blocked_activity.usename as Blocked_user,
postgres-# Blocking_locks.pid as Blocking_pid,
postgres-# Blocking_activity.usename as Blocking_user,
postgres-# Blocked_activity.query as Blocked_statement,
postgres-# Blocking_activity.query as Current_statement_in_blocking_process
postgres-# from Pg_catalog.pg_locks blocked_locks
postgres-# JOIN pg_catalog.pg_stat_activity blocked_activity on blocked_activity.pid = Blocked_locks.pid
postgres-# JOIN pg_catalog.pg_locks Blocking_locks
postgres-# on blocking_locks.locktype = Blocked_locks.locktype
postgres-# and Blocking_locks. DATABASE is not a DISTINCT from Blocked_locks. DATABASE
postgres-# and Blocking_locks.relation is not DISTINCT from blocked_locks.relation
postgres-# and Blocking_locks.page is not DISTINCT from Blocked_locks.page
postgres-# and Blocking_locks.tuple is not DISTINCT from Blocked_locks.tuple
postgres-# and Blocking_locks.virtualxid is not DISTINCT from Blocked_locks.virtualxid
postgres-# and Blocking_locks.transactionid is not DISTINCT from Blocked_locks.transactionid
postgres-# and Blocking_locks.classid is not DISTINCT from Blocked_locks.classid
postgres-# and Blocking_locks.objid is not DISTINCT from Blocked_locks.objid
postgres-# and Blocking_locks.objsubid is not DISTINCT from Blocked_locks.objsubid
postgres-# and Blocking_locks.pid! = Blocked_locks.pid
postgres-#
postgres-# JOIN pg_catalog.pg_stat_activity blocking_activity on blocking_activity.pid = Blocking_locks.pid
postgres-# WHERE not blocked_locks. granted;
Blocked_pid | Blocked_user | Blocking_pid | Blocking_user | blocked_statement | Current_statement_in_blocking_process
-------------+--------------+--------------+---------------+-------------------+------------------------------- --------
(0 rows)

pg< 9.2:

  SELECT BL. pidAs Blocked_pidA. usenameAs Blocked_userKl. pidAs Blocking_pidKa. usenameAs Blocking_userA. current_queryAs Blocked_statementFrom Pg_catalog.pg_locks bl join pg_catalog.pg_stat_activity A on a.procpid = bl.pid  JOIN pg_catalog.pg_locks KL on kl.transactionid = bl.transactionid and kl.pid != bl.pid join pg_catalog.pg_stat_activity ka Span class= "KW1" >on ka.procpid = kl.pid  WHERE not bl. granted; 
 https://wiki.postgresql.org/wiki/Lock_Monitoring 
           

PostgreSQL Lock Monitoring