Postgresql troubleshooting shortcuts

queries.sql

-- Get long queries
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  state,
  datname,
  query
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 seconds' and state != 'idle' order by duration desc;

--
-- Blocked queries, recursive
;with recursive 
    find_the_source_blocker as (
        select  pid
               ,pid as blocker_id
        from pg_stat_activity pa
        where pa.state<>'idle'
              and array_length(pg_blocking_pids(pa.pid), 1) is null

        union all

        select              
                t.pid  as  pid
               ,f.blocker_id as blocker_id
        from find_the_source_blocker f 
        join (  SELECT
                    act.pid,
                    blc.pid AS blocker_id
                FROM pg_stat_activity AS act
                LEFT JOIN pg_stat_activity AS blc ON blc.pid = ANY(pg_blocking_pids(act.pid))
                where act.state<>'idle') t on f.pid=t.blocker_id
        )
select distinct 
       s.pid
      ,s.blocker_id
      ,pb.usename       as blocker_user
      ,pb.query_start   as blocker_start
      ,pb.query         as blocker_query
      ,pt.query_start   as trans_start
      ,pt.query         as trans_query
from find_the_source_blocker s
join pg_stat_activity pb on s.blocker_id=pb.pid
join pg_stat_activity pt on s.pid=pt.pid
where s.pid<>s.blocker_id;

--
-- Blocked queries
select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;