Hey folks --
I am using the pg74.1jdbc3.jar driver against Postgresql 7.4.2 from
within Apache Cocoon, which uses connection pooling (provided by an
Apache Excalibur component). I am running in autocommit false mode and
managing my own transactions with commit/rollback.
I am experiencing connections being left "idle in transaction" after a
web request is complete. The problem is that when I call commit() on
the connection, the JDBC driver issues a "commit;begin;" to the server,
which puts the connection immediately back in a transaction. This
usually wouldn't be a huge problem, but in a connection pooled
environment, the connection is never actually closed, and the connection
will sit idle in transaction.
It seems to me that the driver should only issue a "begin" before the
next sql statement is executed, rather than right after the commit. Or
maybe the begin could be removed all together and let the server
implicitly start the next transaction?
This problem was also just recently brought up on the hackers list:
http://archives.postgresql.org/pgsql-hackers/2004-04/msg00269.php
cheers,
-steve