Hi Folks
I have a serious problem, I am running a java application that connects to postgres v 7.2.3 via jdbc drivers, which is heavily loaded with transaction most of the time, and performance is degrading as the time goes by, I have already explain-analyzed almost all the queries being used by the application and there is not problem, but once the application completes a transaction the connection in the database still hangs on for quite a while as I see lots of idle_in_transaction in pg_monitor all the time.
What I want to know, if you can help, is how the transactions should be handled via jdbc so as soon as a transaction has been completed that connection should be freed from the postgres.
I asked one our developers to check the logic inside the jdbc and according to him it seems that an implicit "begin" is issued (as this
is not an available API call in JDBC) as soon as a commit is issued either auto or not auto. As a result you will always be sitting in an unfinished "begin" only now every SQL statement sent to the backend is processed in its own "begin ... end ... begin" window.
What do you say and how can we overcome this issue.
Thanks so much for your help.
Brenda