[Spacewalk-list] I think I found the root cause of the PostgreSQL Idle in transaction connection build up.

[Paul Robert Marino]

hey every one

Ive been doing some testing and I am fairly positive I found out why
the number of connections in PostgreSQL increases and its not a
spacewalk bug at all.
It looks like its a JDBC bug.
Ive seen chatter about similar problems with other project that use
the PostgreSQL-JDBC driver on Redhat based distros so it got me
curious if the JDBC driver might be the culprit.

using the query
"
 SELECT (now() - pg_stat_activity.xact_start) AS age,
pg_stat_activity.datname, pg_stat_activity.procpid,
pg_stat_activity.usename, pg_stat_activity.waiting,
pg_stat_activity.query_start, pg_stat_activity.client_addr,
pg_stat_activity.client_port, pg_stat_activity.current_query FROM
pg_stat_activity WHERE (pg_stat_activity.xact_start IS NOT NULL) ORDER
BY pg_stat_activity.xact_start;
"

I can see that right from startup with the stock Redhat
PostgreSQL-JDBC driver postgresql-jdbc-8.4.701-8.el6.noarch there are
11 transactions in a " <IDLE> in transaction" state and the number
never goes below that. After time the number of these idle
transactions grow until the database either reaches the max limit on
the number of connections or the number gets startlingly high.

I downloaded postgresql-8.4-703.jdbc4.jar from
http://jdbc.postgresql.org/download.html and manually installed it.
here are the steps
"
wget  http://jdbc.postgresql.org/download/postgresql-8.4-703.jdbc4.jar
cp postgresql-8.4-703.jdbc4.jar /usr/share/java/
chmod 644 /usr/share/java/postgresql-8.4-703.jdbc4.jar
spacewalk-service stop
rm -f /usr/share/java/postgresql-jdbc.jar
 ln -s /usr/share/java/postgresql-8.4-703.jdbc4.jar
/usr/share/java/postgresql-jdbc.jar
spacewalk-service start
"
I immediately noticed a difference
ten seconds after startup completed there were 6 connections in a "
<IDLE> in transaction" state another 30 seconds latter there were
none.
a few minutes latter a spacewalk repo sync triggered automatically
from quartz while it was running I saw 1 connection in the " <IDLE> in
transaction"  state then after it completed there were 0.
now to be clear I do still see a lot (38) of connections in a
completely idle state when i do a " ps ax|grep postgres |grep
spaceschema |grep idle"
but all of the idle in transaction messages go away unless spacewalk
is actually doing something.

Ill need to let this run a few days but the results look good well see
if the number of connections starts growing but I think its stable.

by the way Ive tested this on spacewalk 1.7 I'll try it on 1.8 latter
this week when I get a chance.