PostgreSQL 谁堵塞了谁（锁等待检测）- pg_blocking_pids

一、背景

当一个进程处于等待（被堵塞）状态时，是谁干的？可以使用如下函数，快速得到捣蛋（堵塞别人）的PID。

二、案例

1、会话1

postgres=# begin;  
BEGIN  
postgres=# select * from tbl limit 1;  
   id   | c1 | c2   
--------+----+----  
 918943 |  1 |  0  
(1 row)  
  
postgres=# select pg_backend_pid();  
 pg_backend_pid   
----------------  
          30862  
(1 row)  

2、会话2

postgres=# begin;  

BEGIN  
postgres=# select pg_backend_pid();  
 pg_backend_pid   
----------------  
          30928  
(1 row)  
  
postgres=# truncate tbl;  
  
等待中  

3、会话3

postgres=# begin;  
BEGIN  
postgres=# select pg_backend_pid();  
 pg_backend_pid   
----------------  
          30936  
(1 row)  
  
postgres=# select * from tbl limit 1;  
  
等待中  

4、会话4

postgres=# select pg_backend_pid();  
 pg_backend_pid   
----------------  
          30999  
(1 row)  
  
postgres=# select * from tbl limit 1;  
  
等待中

5、查看导弹PID

postgres=# select pid,pg_blocking_pids(pid),wait_event_type,wait_event,query from pg_stat_activity ;  
  pid  | pg_blocking_pids | wait_event_type |     wait_event      |                                           query                                             
-------+------------------+-----------------+---------------------+-------------------------------------------------------------------------------------------  
 30862 | {}               | Client          | ClientRead          | select pg_backend_pid();  
 30928 | {30862}          | Lock            | relation            | truncate tbl;  
 30936 | {30928}          | Lock            | relation            | select * from tbl limit 1;  
 30999 | {30928}          | Lock            | relation            | select * from tbl limit 1;