Обсуждение: idle in transaction
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. -- Warren Little Senior Vice President Secondary Marketing Security Lending Wholesale, LC www.securitylending.com Tel: 866-369-7763 Fax: 866-849-8082
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
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