postgres阻塞会话查询

1、会话1开启一个会话删除一个表的数据

postgres=# begin;
BEGIN
postgres=# select txid_current();
 txid_current 
--------------
      1050087
(1 row)
postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
          11073
(1 row)

postgres=# delete from t;
DELETE 100000

2、会话2开启一个会话删除同样的表会被阻塞

postgres=# begin;
BEGIN
postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
          11854
(1 row)

postgres=# delete from t;  <=====此步会卡主

3、新开启一个窗口，查询11854被谁阻塞

postgres=# select * from (SELECT 
postgres(# procpid, 
postgres(# start, 
postgres(# now() - start AS lap, 
postgres(# current_query 
postgres(# FROM 
postgres(# (SELECT 
postgres(# backendid, 
postgres(# pg_stat_get_backend_pid(S.backendid) AS procpid, 
postgres(# pg_stat_get_backend_activity_start(S.backendid) AS start, 
postgres(# pg_stat_get_backend_activity(S.backendid) AS current_query 
postgres(# FROM 
postgres(# (SELECT pg_stat_get_backend_idset() AS backendid) AS S 
postgres(# ) AS S 
postgres(# WHERE 
postgres(# current_query <> '<IDLE>' 
postgres(# ORDER BY 
postgres(# lap DESC) b where b.procpid=(select pid
postgres(#   from (select a.locktype,
postgres(#                a.transactionid,
postgres(#                a.virtualtransaction,
postgres(#                b.pid,
postgres(#                a.mode,
postgres(#                a.granted
postgres(#           from pg_locks a, pg_locks b
postgres(#          where a.transactionid = b.transactionid
postgres(#            and a.transactionid is not null
postgres(#            and a.granted = 'f') b
postgres(#  where b.pid != '11854');
 procpid |             start             |       lap       | current_query  
---------+-------------------------------+-----------------+----------------
   11073 | 2020-08-05 11:36:51.662108-04 | 00:22:43.999811 | delete from t;
(1 row)

SQL语句

select * from (SELECT 
procpid, 
start, 
now() - start AS lap, 
current_query 
FROM 
(SELECT 
backendid, 
pg_stat_get_backend_pid(S.backendid) AS procpid, 
pg_stat_get_backend_activity_start(S.backendid) AS start, 
pg_stat_get_backend_activity(S.backendid) AS current_query 
FROM 
(SELECT pg_stat_get_backend_idset() AS backendid) AS S 
) AS S 
WHERE 
current_query <> '<IDLE>' 
ORDER BY 
lap DESC) b where b.procpid=(select pid
  from (select a.locktype,
               a.transactionid,
               a.virtualtransaction,
               b.pid,
               a.mode,
               a.granted
          from pg_locks a, pg_locks b
         where a.transactionid = b.transactionid
           and a.transactionid is not null
           and a.granted = 'f') b
 where b.pid != '11854');