Show blocked locks in PostgreSQL

12 Sep 2015

See for more details, but the quick solution is to create this view and select from it:

create or replace view blocked_locks as
distinct                     as blocked_pid,
         blocked_activity.usename              as blocked_user,
         now() - blocked_activity.query_start  as blocked_duration,
         blocked_activity.query                as blocked_statement,                    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 =
    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          is not distinct from
     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                             !=
    join pg_stat_activity        as blocking_activity 
      on =
   where blocked_locks.granted is false;