PostgreSQL pg_blocking_pids() Function

The PostgreSQL pg_blocking_pids() function returns a list of process IDs of sessions that prevent the specified session from acquiring locks.

pg_blocking_pids() Syntax

Here is the syntax of the PostgreSQL pg_blocking_pids() function:

pg_blocking_pids(pid integer) -> integer[]

Parameters

pid

The process ID of the blocked session.

Return value

The PostgreSQL pg_blocking_pids() function returns an array containing the process IDs of all sessions that prevented the specified session from acquiring locks.

pg_blocking_pids() Examples

The demo usage requires 3 sessions, please follow the steps below for this example.

1. Open a session and log in, use the pg_backend_pid() function see the process ID of the current session:

   SELECT pg_backend_pid();
   

   pg_backend_pid
   ----------------
             1152

   Start a transaction

   BEGIN;
   

   Lock student table:

   LOCK TABLE student IN ACCESS EXCLUSIVE MODE;
   

2. Open a new session and log in, use the pg_backend_pid() function see the process ID of the current session:

   SELECT pg_backend_pid();
   

   pg_backend_pid
   ----------------
             18376

   Start a transaction:

   BEGIN;
   

   Insert a new row into the student table:

   INSERT INTO student (name, gender) VALUES ('Tim', 'M');
   

   You will find that the execution is blocked and never returns.

3. Open a new session and log in, use the pg_blocking_pids() function to see which ones blocked the second session (18376):

   SELECT pg_blocking_pids(18376);
   

   pg_blocking_pids
   ------------------
   {1152}

   1152 is the process ID of the first session. This means that the first session is blocking the second session.