Idle In Transaction Problem - Postgresql

kill-idle-transaction-pg.sh

#!/bin/bash
# Harisfazillah Jamel - 20211216
# Part of script from https://wiki.lyrasis.org/display/DSPACE/Idle+In+Transaction+Problem
# Idle In Transaction Problem

pgprocess_array=( $( /usr/bin/pgrep -f 'idle in transaction' ) )

/usr/bin/pgrep -f 'idle in transaction' | wc -l | logger

for r in "${pgprocess_array[@]}"; do
 /usr/bin/test `/usr/bin/pgrep -f 'idle in transaction' | /usr/bin/wc \-l ` \-gt 0 && /usr/bin/pkill \-o \-f 'idle in transaction'
### /usr/bin/test `/usr/bin/pgrep -f 'idle in transaction' | /usr/bin/wc \-l ` \-gt 20 && /usr/bin/pkill \-o \-f 'idle in transaction'
### /usr/bin/pgrep -f 'idle in transaction' | wc -l
 echo "PG $r"
done

/usr/bin/pgrep -f 'idle in transaction' | wc -l | logger

exit

To check

SELECT count(*),state FROM pg_stat_activity GROUP BY 2;