postgresql 监控会话阻塞

session 阻塞的监控在数据库是重要的监控指标之一。

9.6

with recursive tmp_lock as (
    select distinct
           --w.mode w_mode,w.page w_page,
           --w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start,
           --now()-w.query_start w_locktime,w.query w_query 
           w.pid as id,--w_pid,
           r.pid as parentid--r_pid,
           --r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db,
           --r.relation::regclass,
           --r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start,
           --r.query_start r_query_start,
           --now()-r.query_start r_locktime,r.query r_query,   
    from (
          select a.mode,a.locktype,a.database,
                 a.relation,a.page,a.tuple,a.classid,
                 a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,
                 a.transactionid,
                 b.query as query,
                 b.xact_start,b.query_start,b.usename,b.datname 
           from pg_locks a,
                pg_stat_activity b 
          where a.pid=b.pid 
            and not a.granted 
          ) w,
         (
          select a.mode,a.locktype,a.database,
                 a.relation,a.page,a.tuple,a.classid,
                 a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,
                 a.transactionid,
                 b.query as query,
                 b.xact_start,b.query_start,b.usename,b.datname 
            from pg_locks a,
                 pg_stat_activity b -- select pg_typeof(pid) from pg_stat_activity
           where a.pid=b.pid 
             and a.granted 
          ) r 
    where
        r.locktype is not distinct from w.locktype and
        r.database is not distinct from w.database and
        r.relation is not distinct from w.relation and
        r.page is not distinct from w.page and
        r.tuple is not distinct from w.tuple and
        r.classid is not distinct from w.classid and
        r.objid is not distinct from w.objid and
        r.objsubid is not distinct from w.objsubid and
        r.transactionid is not distinct from w.transactionid and
        r.pid <> w.pid
        
),tmp0 as (
  select *
    from tmp_lock tl
  union all
  --查找root，同一时刻可能有多个root
  select t1.parentid,0::int4
    from tmp_lock t1
   where 1=1
     and t1.parentid not in (select id from tmp_lock)
),tmp3 (pathid,depth,id,parentid) as (
	--对过滤出的机构向下递归，构成tree
	SELECT array[id]::text[] as pathid,1 as depth,id,parentid
	  FROM tmp0
	 where 1=1
	   and parentid=0
	union
	SELECT t0.pathid||array[t1.id]::text[] as pathid,t0.depth+1 as depth,t1.id,t1.parentid
	  FROM tmp0 t1,  
	       tmp3 t0
	 where 1=1
	   and t1.parentid=t0.id
)
select distinct 
       '/'||array_to_string(a0.pathid,'/') as pathid,
	   a0.depth,
       a0.id,a0.parentid,lpad(a0.id::text, 2*a0.depth-1+length(a0.id::text),' ') as tree_id,
       a2.datname,a2.usename,a2.application_name,a2.client_addr,a2.wait_event_type,a2.wait_event,a2.state
  from tmp3 a0
       left outer join (select distinct '/'||id||'/' as prefix_id,id
	                      from tmp0
						 where 1=1 ) a1
	                on position( a1.prefix_id in '/'||array_to_string(a0.pathid,'/')||'/' ) >0
	   left outer join pg_stat_activity a2 -- select * from pg_stat_activity
	                on a0.id = a2.pid
order by '/'||array_to_string(a0.pathid,'/'),a0.depth  
; 

       pathid       | depth |  id   | parentid |  tree_id   | datname  | usename  | application_name | client_addr | wait_event_type |  wait_event   |        state        |         backend_start         |          xact_start           |          query_start          
--------------------+-------+-------+----------+------------+----------+----------+------------------+-------------+-----------------+---------------+---------------------+-------------------------------+-------------------------------+-------------------------------
 /10180             |     1 | 10180 |        0 |  10180     | postgres | postgres | psql             |             |                 |               | idle in transaction | 2018-11-05 15:25:56.473886+08 | 2018-11-05 15:26:12.529745+08 | 2018-11-05 16:30:50.347912+08
 /10180/10197       |     2 | 10197 |    10180 |    10197   | postgres | postgres | psql             |             | Lock            | transactionid | active              | 2018-11-05 15:26:02.12397+08  | 2018-11-05 15:26:30.767745+08 | 2018-11-05 15:26:30.767745+08
 /10180/10197/10508 |     3 | 10508 |    10197 |      10508 | postgres | postgres | psql             |             | Lock            | tuple         | active              | 2018-11-05 16:38:02.858333+08 | 2018-11-05 16:38:19.514655+08 | 2018-11-05 16:38:19.514655+08
 /9782              |     1 |  9782 |        0 |  9782      | postgres | postgres | psql             |             |                 |               | idle in transaction | 2018-11-05 15:05:04.656155+08 | 2018-11-05 15:11:45.731423+08 | 2018-11-05 15:46:24.559188+08
 /9782/10026        |     2 | 10026 |     9782 |    10026   | postgres | postgres | psql             |             | Lock            | transactionid | active              | 2018-11-05 15:24:02.389744+08 | 2018-11-05 15:50:16.546751+08 | 2018-11-05 15:50:38.428968+08
 /9782/10026/9918   |     3 |  9918 |    10026 |      9918  | postgres | postgres | psql             |             | Lock            | tuple         | active              | 2018-11-05 15:11:31.394268+08 | 2018-11-05 15:50:19.964021+08 | 2018-11-05 15:50:45.080284+08
(6 rows)