Blocked and blocking sessions (PostgreSQL)

PostgreSQLRead onlyScripts Gallery

During an incident: which sessions wait for a lock, and which sessions hold it.

Verification status. Written from the BroadSQL documentation and the PostgreSQL documentation. It has not been run by the BroadSQL team: run it first on a test database, and read its result critically.

When to use it

A query or an application hangs.
Find the session to investigate before anyone terminates anything.

The Script

Download pg-blocking-sessions.sql

-- @description: Sessions waiting for a lock, and the sessions that block them (PostgreSQL)
-- @tags: postgresql, diagnostics, locks, incident
-- @status: draft
-- @access: read
-- @dialect: PostgreSQL
SELECT a.pid,
       pg_blocking_pids(a.pid)::text AS blocked_by,
       a.usename,
       a.state,
       (now() - a.query_start)::text AS waiting_for,
       LEFT(a.query, 200) AS query
  FROM pg_stat_activity a
 WHERE cardinality(pg_blocking_pids(a.pid)) > 0
 ORDER BY a.query_start;

Parameters

This Script takes no parameter.

Install and run it

Save the file in your Scripts Library folder (scripts in the installation folder by default), for example as gallery/pg-blocking-sessions.sql.
Connect to the PostgreSQL database with an account that can see other sessions.
Check what it declares: LIB SHOW gallery/pg-blocking-sessions.sql;
Run it: @gallery/pg-blocking-sessions.sql

What you get

One row per waiting session: its process ID, the IDs of the sessions that block it (pg_blocking_pids), its user, state, how long it has waited and the start of its query.

Limits

Ordinary users see full details of their own sessions only; the role pg_read_all_stats or a superuser sees all of them.
It reads; it never terminates a session.
Watch it with REPEAT @gallery/pg-blocking-sessions.sql EVERY 10s FOR 10m;.
Written from the PostgreSQL documentation of pg_stat_activity and pg_blocking_pids.

Found something wrong or unclear on this page? Tell us, with the page address.