#!/bin/bash

threads() {
tlist=$(psql -U postgres -q -t -v max_time="'00:30:00'" <<_THREADS_
BEGIN;

SELECT a.datname as "DBName"
     , a.usename as "User"
     , case when a.client_addr is null then 'local' else host(a.client_addr) end as "IP"
     , case when a.state = 'idle' then '00:00:00.000'
            else to_char(age(timeofday()::timestamp,a.query_start),'HH24:MI:SS.MS')
        end as "Elapsed"
     , substring(ltrim(regexp_replace(a.query, E'[\\n\\f\\t\\r ]+',' ','g')) for 61) as "Query"
     , a.pid as "OS PID"
  FROM pg_stat_activity a
 WHERE state = 'idle in transaction'
   AND age(timeofday()::timestamp, query_start) > :max_time
   AND a.usename NOT IN (SELECT rolname
                           FROM pg_authid
                          WHERE rolsuper = TRUE)
ORDER BY
         state != 'idle' desc,
         query not like '<idle>%' desc,
         age(timeofday()::timestamp,query_start) desc;


COMMIT;

_THREADS_
)
}

threads
#echo "$tlist"

#echo "$tlist" | xargs --max-lines=1 --no-run-if-empty logger -t postgresql killing
echo "$tlist" | cut --delimiter="|" --fields=6- | xargs --max-lines=1 --no-run-if-empty kill