Skip to content

Instantly share code, notes, and snippets.

@stultus

stultus/blocking_sessions_postgress.txt

Created December 17, 2019 06:45
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save stultus/b619b0caccac9a35d317a8d5b643a754 to your computer and use it in GitHub Desktop.
Save stultus/b619b0caccac9a35d317a8d5b643a754 to your computer and use it in GitHub Desktop.
Download ZIP
script to find the cause of blocked transactions.
Raw
blocking_sessions_postgress.txt
SELECT
pl.pid as blocked_pid
,psa.usename as blocked_user
,pl2.pid as blocking_pid
,psa2.usename as blocking_user
,psa.query as blocked_statement
FROM pg_catalog.pg_locks pl
JOIN pg_catalog.pg_stat_activity psa
ON pl.pid = psa.pid
JOIN pg_catalog.pg_locks pl2
JOIN pg_catalog.pg_stat_activity psa2
ON pl2.pid = psa2.pid
ON pl.transactionid = pl2.transactionid
AND pl.pid != pl2.pid
WHERE NOT pl.granted;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment