See blocked queries and the queries that are blocking them in Postgres.

gistfile1.sql

with
blocked as (
  select 
    pl.transactionid,
    pl.pid blocked_pid, pl.mode blocked_mode,
    psa.usename blocked_user, psa.application_name blocked_application, 
    now() - psa.query_start blocked_duration, psa.xact_start blocked_xact_start, psa.query_start blocked_query_start,
    psa.current_query blocked_statement
  from pg_stat_activity psa
  inner join pg_locks pl on pl.pid = psa.procpid
  where not pl.granted
),
blocking as (
  select 
    pl.transactionid,
    pl.pid blocking_pid, pl.mode blocking_mode,
    psa.usename blocking_user, psa.application_name blocking_application, 
    now() - psa.xact_start blocking_duration, psa.xact_start blocking_xact_start, psa.query_start blocking_query_start,
    psa.current_query blocking_statement
  from pg_stat_activity psa
  inner join pg_locks pl on pl.pid = psa.procpid
  where pl.granted
),
blocks as (
  select * from blocked
  natural inner join blocking 
  order by blocked.blocked_duration desc
)
select * from blocks;