pg_blocking_pids()

A function listing the sessions preventing a session from acquiring a lock

pg_blocking_pids() is a system function returning a list of the sessions which are blocking the specified session from acquiring a particular lock.

pg_blocking_pids() was added in PostgreSQL 9.6.

Usage

pg_blocking_pids ( integer ) → integer[]

The output is an array of session (backend) process IDs blocking the specified session, or an empty array if there are no blocking sessions.

Note the following caveats:

  • for parallel queries, the session PID where the query was executed is listed, not that of the parallel worker, meaning there may be duplicate PIDs
  • when a prepared transaction holds a conflicting lock, it will be represented by PID 0

Note that execution of pg_blocking_pids() requires exclusive access to the lock manager's shared state for a short period, which may have performance implications, particularly if frequently called.

Change history

Examples

Assuming the following table is locked in one session, e.g.:

postgres=# BEGIN;
BEGIN

postgres=*# LOCK TABLE foo IN ACCESS EXCLUSIVE MODE;
LOCK TABLE

and another session is attempting to modify the table, e.g.:

postgres=# SELECT pg_backend_pid();
 pg_backend_pid 
----------------
        4154357
(1 row)

postgres=# BEGIN;
BEGIN

postgres=*# INSERT INTO foo VALUES(1);

a query such as the following will list details about blocked sessions:

postgres=# SELECT pid, pg_blocking_pids(pid), query
             FROM pg_stat_activity
            WHERE backend_type = 'client backend'
              AND wait_event_type = 'Lock';
   pid   | pg_blocking_pids |           query            
---------+------------------+----------------------------
 4154357 | {4154298}        | INSERT INTO foo VALUES(1);
(1 row)

Output of pg_blocking_pids() when no blocking sessions are present:

postgres=# SELECT * FROM pg_blocking_pids(pg_backend_pid());
 pg_blocking_pids
------------------
 {}
(1 row)

Categories

Locking, System function

See also

pg_safe_snapshot_blocking_pids(), pg_locks, pg_backend_pid(), PID