myorama
2/14/2017 - 2:39 PM

Detect blocked and blocking sessions in PostgreSQL 9.6+

Detect blocked and blocking sessions in PostgreSQL 9.6+

SELECT pid, state,
 CASE WHEN wait_event is not null
  THEN concat(wait_event, ' (', wait_event_type, ')')
 ELSE NULL::text END AS lock_intel,
  pg_blocking_pids(pid) AS blocked_by,
 substr(query, 1, 20)
FROM pg_stat_activity
WHERE state IS NOT NULL;