top of page

Postgres, including blocking sessions last command in Active Sessions List

Writer: kyle Hailey
kyle Hailey
Aug 24, 2023
1 min read


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


 
 
 

2 Comments


scritchy scratchy game
Aug 27

This article hits on a crucial aspect of Postgres monitoring that can save a lot of headaches during high-stress situations. It's always useful to have visibility into what blockers are doing, as mentioned in the article. For more in-depth insights and strategies on managing Postgres performance, you might find the scritchy scratchy game helpful, as it covers a range of topics including this.

Like

Guest
Aug 10

Rather than making large movements Snow Rider, try using gentle taps to adjust your position. Small corrections are much safer than sudden swerves, especially once the sled reaches higher speeds.

Like

Kyle Hailey

  • Facebook
  • Twitter
  • LinkedIn
  • Instagram

San Franisco, Ca 94131

415-341-3430  (please text initially before calling)

bottom of page