-
-
Save camallen/82638af12d0ee344f3b11b2602cc4401 to your computer and use it in GitHub Desktop.
Show info on postgres locks
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 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; | |
| # better blocking locks query | |
| SET application_name='panoptes'; | |
| SELECT blocked_locks.pid AS blocked_pid, | |
| blocked_activity.usename AS blocked_user, | |
| blocking_locks.pid AS blocking_pid, | |
| blocking_activity.usename AS blocking_user, | |
| blocked_activity.query AS blocked_statement, | |
| blocking_activity.query AS current_statement_in_blocking_process, | |
| blocked_activity.datname AS blocked_application | |
| FROM pg_catalog.pg_locks blocked_locks | |
| JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid | |
| JOIN pg_catalog.pg_locks 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 blocking_activity ON blocking_activity.pid = blocking_locks.pid | |
| WHERE NOT blocked_locks.GRANTED; | |
| -- useful links for more detailed queries https://gist.github.com/rgreenjr/3637525 | |
| -- info on non-idle connections for the whole db | |
| select pid, application_name, client_addr, waiting, state, now() - query_start as "runtime", query from pg_stat_activity | |
| where state <> 'idle' | |
| order by runtime | |
| -- find info on active queries | |
| select pid, application_name, client_addr, waiting, state, query from pg_stat_activity | |
| where datname = 'panoptes' | |
| AND usename = 'panoptes' | |
| and state = 'active' | |
| --and pid = X; | |
| -- kill a query | |
| select pg_cancel_backend(pid) | |
| -- kill a list of long running queries via a select call | |
| SELECT pg_cancel_backend(pid) from pg_stat_activity | |
| where state <> 'idle' | |
| and application_name like 'puma%' | |
| and now() - query_start > interval '30 second' | |
| order by now() - query_start | |
| limit 5 | |
| select * from pg_stat_activity | |
| /* This lists queries causing locks. */ | |
| select pid, usename, pg_blocking_pids(pid) as blocked_by, query as blocked_query | |
| from pg_stat_activity | |
| where cardinality(pg_blocking_pids(pid)) > 0; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment