Re: pg_stat_activity

Ron,

This might get you closer to you goal. Select will not take out exclusive locks out so we can eliminate sessions that have locks out. Note that this will also remove long running queries that are part of a larger transaction that had issued some sort of DML/DDL prior to running.

p.s. careful with the smart quotes; autocorrect in email is not great for code.

select pg_stat_activity.pid
, pg_stat_activity.usename
, pg_stat_activity.client_addr
, pg_stat_activity.client_port
, pg_stat_activity.state
, pg_stat_activity.backend_xid
, pg_stat_activity.backend_xmin
, age(now(), pg_stat_activity.state_change) as state_time
, pg_stat_activity.wait_event_type
, pg_stat_activity.wait_event
, age(now(), pg_stat_activity.query_start) as query_time
, pg_stat_activity.query
from pg_stat_activity
where pg_stat_activity.state <> 'idle'::text
and pg_stat_activity.pid not in (
select pid
from pg_locks
where locktype = 'relation'
and mode in ('RowExclusiveLock', 'ExclusiveLock')
)
order by pg_stat_activity.query_start
;