top of page
  • Writer's picturekyle Hailey

Postgres, including blocking sessions last command in Active Sessions List



I love pg_stats_activity. It'sthe heart of all my postgres performance monitoring. It's about the only place I go. But one thing that had been super frustrating, especially when the house is burning down, is that when I saw blockers mucking up the system, I didn't know what they were doing and/or if I could kill them. So newest modification is to include them in the output even if they are "idle".


Output looks like:




script

select
  	case when b.bpids::integer = a.pid 
         then a.pid
         else null end as blocker,
    COALESCE(wait_event,'cpu'),
	COALESCE(round(EXTRACT(EPOCH FROM (clock_timestamp() - 
	     query_start))::numeric, 5), -1)  as query_age , 
	CASE when lower(substr(query , 1, 5)) = 'alter' 
	      then 999999 
	      else
          COALESCE(round(EXTRACT(EPOCH FROM (clock_timestamp() - 
          xact_start ))::numeric, 5), -1) end  as xact_age,
	pid::text,    
	pg_blocking_pids(PID)::text as blocker,  
	state,
    substr(query , 1, 80)
    -- , query 
    --  , * 
from pg_stat_activity a,
    (SELECT COALESCE(
      (SELECT unnest(string_to_array(replace(replace(pg_blocking_pids(PID)::text, '{', ''), '}', ''), ','))::int
       FROM pg_stat_activity  ), -1 ) bpids
 ) b
  where
   (     state='active' 
     or pid=3379309 
     or PID = b.bpids::integer  
   )
  --and 
--  pid != pg_backend_pid()
 -- pid=3379309
--     pid in (3533517,3651820,3651819)
order by 3 desc, 5 ,  2 desc
-- \watch 1
;

I live the comments in because these I things I often modify on the fly.

Like restricting it to one PID or making sure to include a specific PID


142 views0 comments

Comments


bottom of page