Show blocked locks in PostgreSQL

12 Sep 2015

See https://wiki.postgresql.org/wiki/Lock_Monitoring for more details, but the quick solution is to create this view and select from it:

create or replace view blocked_locks as
select
distinct blocked_locks.pid                     as blocked_pid,
         blocked_activity.usename              as blocked_user,
         now() - blocked_activity.query_start  as blocked_duration,
         blocked_activity.query                as blocked_statement,
         blocking_locks.pid                    as blocking_pid,
         blocking_activity.usename             as blocking_user,
         blocking_activity.query               as blocking_statement,
         now() - blocking_activity.query_start as blocking_duration
    from pg_locks                as blocked_locks
    join pg_stat_activity        as blocked_activity  
      on blocked_activity.pid = blocked_locks.pid
    join pg_locks                as blocking_locks 
      on blocking_locks.locktype                         = blocked_locks.locktype
     and blocking_locks.database      is not distinct from blocked_locks.database
     and blocking_locks.relation      is not distinct from blocked_locks.relation
     and blocking_locks.page          is not distinct from blocked_locks.page
     and blocking_locks.tuple         is not distinct from blocked_locks.tuple
     and blocking_locks.virtualxid    is not distinct from blocked_locks.virtualxid
     and blocking_locks.transactionid is not distinct from blocked_locks.transactionid
     and blocking_locks.classid       is not distinct from blocked_locks.classid
     and blocking_locks.objid         is not distinct from blocked_locks.objid
     and blocking_locks.objsubid      is not distinct from blocked_locks.objsubid
     and blocking_locks.pid                             != blocked_locks.pid
    join pg_stat_activity        as blocking_activity 
      on blocking_activity.pid = blocking_locks.pid
   where blocked_locks.granted is false;