Re: Transactions not idle but "idle in transaction" since

Christian van der Leeden wrote:

> Hi,
>
>     I've upgraded the postgres system to 7.4.2 recently and switched
> the JDBC driver to 7.4.213. Now all my transactions are always
> "idle in transaction" instead of "idle" (when doing ps -U postgres).
> I'm using WebObjects, which will set setAutocommit(true) while doing
> selects and just setAutocommit(false) when doing insert/updates.
>
> Is the autocommit feature now implemented in the JDBC adaptor (and not
> on the server side)? I couldn't find the logic of doing rollback/commits
> on the client side after each expression in AbstractJdbc1Connection.java
> (which comes with 7.4.2 distribution).

7.3 added a way to disable server-side autocommit; 7.4 removed it again.
The default server behaviour is autocommit ON, so the JDBC driver does
extra work to implement autocommit=off (it inserts BEGIN/COMMIT/ROLLBACK
as needed).

> I'm guessing that transactions being idle in transaction could be the cause
> of my problem, that postgres is eating away virtual memory (on OSX10.3),
> and I'd like to try to get all selects commited or rolled back...

It's probably harmless; the driver does a "COMMIT;BEGIN" or
"ROLLBACK;BEGIN" when committing or rolling back, so there always
appears to be a transaction open. Of course it's also possible your app
really IS leaving the transaction open -- that'd be an application bug.

If you upgrade to the latest CVS driver this behaviour should go away
(the BEGIN is delayed until the first query is run).

-O