#!/bin/bash

# kills idle queries and running for more than 120 mins

HOSTNAME=`hostname`
PSQL="/usr/bin/psql"
PORT=5432
HOST="localhost"
DB="pos"
USER="root"
TMP_F="/tmp/out.$$.csv"

$PSQL -U $USER -d $DB -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle' AND now() - query_start > '00:05:00';" > /dev/null

$PSQL -d $DB -U $USER -p $PORT -o $TMP_F <<EOF
SELECT pid, now() - query_start as "runtime"
  FROM  pg_stat_activity
  WHERE now() - query_start > '120 minutes'::interval
 ORDER BY runtime DESC;
EOF

re='^[0-9]+$'

for pid in `cat $TMP_F | grep -v runtime | cut -f1 -d"|"`; do
  if [[ $pid =~ $re ]] ; then
    $PSQL -d $DB -U $USER -p $PORT <<EOF
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid=$pid;
EOF
  fi
done

rm -f $TMP_F

