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