Re: idle in transaction

Oliver,
Thanks for the explanation.
This is consistent with my move from 7.3 to 7.4. We have a some
erroneous code in our system which is executing some statements and not
closing out the transaction with a proper rollback or commit. The "idle
in transaction" was revealing this. With the 7.4 implementation I'll
need to dig deeper to find the culprit.

On Mon, 2004-02-16 at 02:32, Oliver Jowett wrote:
> 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
--
Warren Little
Senior Vice President
Secondary Marketing
Security Lending Wholesale, LC
www.securitylending.com
Tel: 866-369-7763
Fax: 866-849-8082