#!/bin/sh

# idproc
# Sun Mar 09 19:06:58 MST 2014 
# jorgericarte@asuni.com.mx
# Elimina procesos con SELECT
# Ultimo cambio: Sun 09 Mar 2014 09:32:36 PM MST

if [ $# -eq 0 ]		# Validando si tiene parametros
then 
  ps ax | grep "postgres:" | grep "idle in transaction"
  ps ax | grep "postgres:" | grep SELECT
  exit 1
fi


if [ ! -z "$1" ]
then
  SQL1="SELECT datid, datname, procpid, usename, current_query, 
        waiting, xact_start, query_start, client_addr, client_port 
        FROM pg_stat_activity 
        WHERE procpid IN ($1) 
        ORDER BY query_start; " 
  psql -x -c "$SQL1" 
fi

if [ ! -z "$2" ]
then
  SQL2="SELECT pg_class.relname, pg_locks.mode, substr(pg_stat_activity.current_query,1,30), 
        age(now(),pg_stat_activity.query_start) as \"age\", pg_stat_activity.procpid 
        FROM pg_stat_activity,pg_locks 
        LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)  
        WHERE pg_locks.pid=pg_stat_activity.procpid 
          AND pg_stat_activity.datname = '$2' 
          AND pg_stat_activity.procpid in ($1) 
        ORDER BY age DESC;"
  psql -c "$SQL2" 
fi
if [ ! -z "$3" ]
then 
  SQL3="SELECT pg_terminate_backend($1)"
  psql -c "$SQL3"
fi

exit 0