4thdoctor_scarf

get blocking queries

Jul 30th, 2018
323
0
Never
1
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.11 KB | None | 0 0
  1. CREATE VIEW blocking_procs AS
  2. SELECT
  3. kl.pid as blocking_pid,
  4. ka.usename as blocking_user,
  5. ka.query as blocking_query,
  6. bl.pid as blocked_pid,
  7. a.usename as blocked_user,
  8. a.query as blocked_query,
  9. to_char(age(now(), a.query_start),'HH24h:MIm:SSs') as age
  10. FROM pg_catalog.pg_locks bl
  11. JOIN pg_catalog.pg_stat_activity a
  12. ON bl.pid = a.pid
  13. JOIN pg_catalog.pg_locks kl
  14. ON bl.locktype = kl.locktype
  15. and bl.database is not distinct from kl.database
  16. and bl.relation is not distinct from kl.relation
  17. and bl.page is not distinct from kl.page
  18. and bl.tuple is not distinct from kl.tuple
  19. and bl.virtualxid is not distinct from kl.virtualxid
  20. and bl.transactionid is not distinct from kl.transactionid
  21. and bl.classid is not distinct from kl.classid
  22. and bl.objid is not distinct from kl.objid
  23. and bl.objsubid is not distinct from kl.objsubid
  24. and bl.pid <> kl.pid
  25. JOIN pg_catalog.pg_stat_activity ka
  26. ON kl.pid = ka.pid
  27. WHERE kl.granted and not bl.granted
  28. ORDER BY a.query_start;
Advertisement
Comments
  • User was banned
Add Comment
Please, Sign In to add comment