Re: idle in transaction

Warren Little wrote:
> I recently posted this on pgsql-admin and was directed to try this list
> as well.
>
> We recently upgraded postgres from 7.3 to 7.4, along with the JDBC jar,
> and noticed all the backend processes/connections are left in the "idle
> in transaction" state where before they where left in the "idle" state.
> Has something changed in the 7.4 jdbc driver vs 7.3 which might be
> causing this?
> Note
> We call setAutoCommit(FALSE) on every connection when created.

The driver does a "commit; begin" when commit() is called. In an ideal
world it'd delay the 'begin' until the first statement is executed (and
batch execution with that statement to avoid an extra roundtrip) but
currently it doesn't. There is a TODO comment to this effect in the driver.

This area has certainly changed since 7.3, most notably how server-side
autocommit (not JDBC autocommit) is handled was changed. The 7.3 driver
appears to have used 'set autocommit = off' with a 7.3 driver vs. a 7.3
server, but it reverted to using commit;begin in all cases after
server-side autocommit disappeared again in 7.4.

-O