pg_blocking_pids()
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
- PostgreSQL 9.6
- added (commit 52f5d578)
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)
References
- PostgreSQL documentation: Session Information Functions
Useful links
- Postgres 9.6 feature highlight - pg_blocking_pids - July 2016 blog article by Michael Paquier