Daily Hack #day44 - Postgres Trick: Find Idle/Blocked Processes

This query looks at the pg_stat_activity view for processes that are active but have a wait_event or wait_event_type that are non-NULL.

SELECT
        pid,
        datname,
        usename,
        application_name,
        client_addr,
        client_port,
        to_char (now (), 'YYYY-MM-DD HH24:MI:SS') as now,
        to_char (now () - xact_start, 'DD HH24:MI:SS MS') as xact_time,
        to_char (now () - query_start, 'DD HH24:MI:SS MS') as query_time,
        state,
        to_char (now () - state_change, 'DD HH24:MI:SS MS') as state_time,
        wait_event,
        wait_event_type,
        left (query, 40)
      FROM
        pg_stat_activity
      WHERE
        state != 'idle'
        and pid != pg_backend_pid ()
      ORDER BY
        query_time desc;