Thread: Transactions not idle but "idle in transaction" since 7.4 and JDBC 7.4.213

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).

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...

Thanks

Christian


-----------------------------------------------
Christian van der Leeden
Software Engineering

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