idle in transaction

[Warren Little]

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: [phone]
Fax: [phone]

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Soeren Laursen]

Just a very quick thought, have You issued a:
connection.commit();

Regards,

Søren,
Subject: [ADMIN] idle in transaction
From: Warren Little <***@securitylending.com>
To: pgsql-***@postgresql.org
Date sent: Sun, 15 Feb 2004 09:23:32 -0700

Post by Warren Little
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: [phone]
Fax: [phone]
---------------------------(end of broadcast)---------------------------

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Marc Mitchell]

We see the exact same condition when setting "setAutoCommit(FALSE)".
However, we expected that situation based on what we understand as the
way such a setting manifests itself within the
Multi-Statement-Transaction handling of Postgres. Furthermore, we saw
this exact same behavior under at least 7.3.4, if not 7.3.2 and maybe
even 7.2.something.

So long as the idled transaction isn't holding any locks on any data
resources, I don't know if this condition is a bad thing. It would be
nice to be able to differentiate between a transaction that has been
"declared" but has yet to really begin issuing any statements and take
locks from transactions that are idle "mid-transaction". The latter of
which spells death for a multi-user, OLTP application.

I'll be curious to hear others experience with this.

Marc Mitchell
Enterprise Information Solutions, Inc.
Downers Grove, IL 60515
***@eisolution.com

-----Original Message-----
From: pgsql-admin-***@postgresql.org
[mailto:pgsql-admin-***@postgresql.org] On Behalf Of Warren Little
Sent: Sunday, February 15, 2004 10:24 AM
To: pgsql-***@postgresql.org
Subject: [ADMIN] idle in transaction


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: [phone]
Fax: [phone]

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Post by Marc Mitchell
So long as the idled transaction isn't holding any locks on any data
resources, I don't know if this condition is a bad thing. It would be
nice to be able to differentiate between a transaction that has been
"declared" but has yet to really begin issuing any statements and take
locks from transactions that are idle "mid-transaction".

You guys should probably take this to the pgsql-jdbc list, since that's
where the people who know the innards of the JDBC driver hang out.

I just saw a patch go by on that list that purports to
* Keeps track of the current transaction state.
* Prevents starting a new transaction until actually required.
so that may address your concern. I'm not enough of a JDBC hacker
to be sure though.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)