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:


  	case when b.bpids::integer = 
         else null end as blocker,
	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 
          COALESCE(round(EXTRACT(EPOCH FROM (clock_timestamp() - 
          xact_start ))::numeric, 5), -1) end  as xact_age,
	pg_blocking_pids(PID)::text as blocker,  
    substr(query , 1, 80)
    -- , query 
    --  , * 
from pg_stat_activity a,
      (SELECT unnest(string_to_array(replace(replace(pg_blocking_pids(PID)::text, '{', ''), '}', ''), ','))::int
       FROM pg_stat_activity  ), -1 ) bpids
 ) b
   (     state='active' 
     or pid=3379309 
     or PID = b.bpids::integer  
--  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

98 views0 comments
bottom of page