Skip to content

Instantly share code, notes, and snippets.

@parrish
Created June 30, 2016 14:33
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save parrish/69ac0137f773306c626681b1eda953b3 to your computer and use it in GitHub Desktop.
Save parrish/69ac0137f773306c626681b1eda953b3 to your computer and use it in GitHub Desktop.
Show info on postgres locks
SELECT bl.pid AS blocked_pid,
a.usename AS blocked_user,
ka.query AS current_statement_in_blocking_process,
now() - ka.query_start AS blocking_duration,
kl.pid AS blocking_pid,
ka.usename AS blocking_user,
a.query AS blocked_statement,
now() - a.query_start AS blocked_duration
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a ON a.pid = 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 ON ka.pid = kl.pid
WHERE NOT bl.GRANTED;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment