本节介绍了函数pg_blocking_pids,该函数用于获取哪些进程(输出参数)阻塞了某个进程(输入参数).

我们在执行某些操作时,console可能会挂起没有输出,这时候你没有办法判断是因为执行很慢还是因为被阻塞了,通过pg_blocking_pids可以判断是否存在阻塞.

----------- session 1
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 2.877 ms
[local]:5432 pg12@testdb=#* delete from tbl where id = 1;
DELETE 1
Time: 1.113 ms
----------- session 2
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 1.700 ms
[local]:5432 pg12@testdb=#* delete from tbl where id = 1;
----------- session 3
[local]:5432 pg12@testdb=# select * from pg_blocking_pids(1628);
 pg_blocking_pids 
------------------
 {1541}
(1 row)
Time: 1.838 ms
[local]:5432 pg12@testdb=#

通过查询可发现阻塞pid = 1628的进程1541.这时候可以查询pg_locks和进程活动信息获取更详细的信息

[local]:5432 pg12@testdb=# select pid,locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransaction,granted,fastpath from pg_locks where relation='tbl'::regclass;
-[ RECORD 1 ]------+--------------------
pid                | 1628
locktype           | relation
relation           | tbl
mode               | RowExclusiveLock
page               | 
tuple              | 
virtualxid         | 
transactionid      | 
virtualtransaction | 5/27
granted            | t
fastpath           | t
-[ RECORD 2 ]------+--------------------
pid                | 1541
locktype           | relation
relation           | tbl
mode               | RowExclusiveLock
page               | 
tuple              | 
virtualxid         | 
transactionid      | 
virtualtransaction | 3/123
granted            | t
fastpath           | t
-[ RECORD 3 ]------+--------------------
pid                | 1628
locktype           | tuple
relation           | tbl
mode               | AccessExclusiveLock
page               | 0
tuple              | 1
virtualxid         | 
transactionid      | 
virtualtransaction | 5/27
granted            | t
fastpath           | f
Time: 4.863 ms

进程活动信息

[local]:5432 pg12@testdb=# select * from pg_stat_activity where pid = 1541;
-[ RECORD 1 ]----+------------------------------
datid            | 16384
datname          | testdb
pid              | 1541
usesysid         | 10
usename          | pg12
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2019-08-14 10:46:54.422873+08
xact_start       | 2019-08-14 12:06:14.357368+08
query_start      | 2019-08-14 12:06:16.982161+08
state_change     | 2019-08-14 12:06:16.983058+08
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      | 625
backend_xmin     | 
query            | delete from tbl where id = 1;
backend_type     | client backend
Time: 8.979 ms

参考资料
How to check the lock level taken by operations in PostgreSQL