[Cayenne-user] postgres, idle in transaction

Per last comment from the link you posted:

"The 'idle' processes are correct behavior; they are the shared
persistent connections to the database the connection pool leaves
open to accelerate database access. Only when you have hanging 'idle
in transaction' threads do you have a problem, as those consume
connections permanently since they are hung processes and are not
shared."

I think that's what you are seeing - the active connection pool. No
need to worry unless your app starts hanging, or the pool size starts
unexpectedly going up.

Andrus

hi,

we are still experiencing trouble with our postgres db and connections hanging "idle in transaction".

we debugged the postgres driver and found out that he starts a transaction on every select-query but does not close it.

cayenne does not seem to bother and re-uses these connections for the next query, but other apps have trouble with the transactions, cause they lock the used tables.

funny thing is that we cannot reproduce this failure with our test-environment wich has the exact same setup as our live-servers...

currently we are doing a commitChanges() after every select-query as a workaround. setting autoCommit to true would have the same effect, but i dont like that idea...

kind regards,
peter

Hi Peter,

Thanks for reporting the details of the issue. I just ran a few
select queries in debugger to confirm that even select queries commit
the transaction before returning connection to the pool (they do).
So, just to doublecheck:

* Do you have other code outside Cayenne using the same connection pool?
* Are you using external transactions (i.e. is "Container Managed
Transactions" checkbox is checked)? (I don't think you've mentioned
this earlier in this thread?)

But anyways, I think regardless of whether Cayenne leaks (something I
still can't confirm) or not, I think we should log this as a bug in
Cayenne connection pool, and ensure that all connections returned to
the pool are rolled back by the PoolManager. Could you please log a
bug report in Jira [1].

Also could you possibly switch the DataSource to DBCP [2] and see if
that DBCP DataSource does the right thing? This may be an easier/
cleaner workaround than committing before queries.

[1] https://issues.apache.org/cayenne/
[2] http://cayenne.apache.org/doc20/dbcpdatasourcefactory.html

Thanks
Andrus