Detecting lock problems

  • Find blocking SQL Queries
SELECT as blocked_pid, a.usename as blocked_user, as blocking_pid, ka.usename as blocking_user, a.query as blocked_statement
from pg_catalog.pg_locks bl join pg_catalog.pg_stat_activity a on = join pg_catalog.pg_locks kl join pg_catalog.pg_stat_activity ka on = on bl.transactionid = kl.transactionid and !=
where not bl.granted;  


  • Find a stuck lock
SELECT quote_ident(nspname) || '.' || quote_ident(relname) as table, a.query from pg_class c, pg_namespace n, pg_locks l, pg_stat_activity a  
where c.relnamespace = n.oid and l.relation = c.oid and l.granted = 't' and = and relation in (SELECT relation from pg_locks where granted = 'f');  
  • Running queries can be terminated with

    pg_cancel_backend(backend pid)

  • Un-responsive backends can be terminated with

    pg_terminate_backend (backend pid)