-
-
Save marklr/16c981f44263cf60121c985b33586e26 to your computer and use it in GitHub Desktop.
PostgreSQL query to display blocked and blocking queries. Updated from PG Wiki.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT blocked_locks.pid AS blocked_pid, | |
blocked_activity.usename AS blocked_user, | |
now() - blocked_activity.query_start | |
AS blocked_duration, | |
blocking_locks.pid AS blocking_pid, | |
blocking_activity.usename AS blocking_user, | |
now() - blocking_activity.query_start | |
AS blocking_duration, | |
blocked_activity.query AS blocked_statement, | |
blocking_activity.query AS blocking_statement | |
FROM pg_catalog.pg_locks AS blocked_locks | |
JOIN pg_catalog.pg_stat_activity AS blocked_activity | |
ON blocked_activity.pid = blocked_locks.pid | |
JOIN pg_catalog.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_catalog.pg_stat_activity AS blocking_activity | |
ON blocking_activity.pid = blocking_locks.pid | |
WHERE NOT blocked_locks.granted; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment