Re: Connection Idle in transaction

Hi all,
I'm facing a problem with the unfamous:

"idle in transaction"

problem. I'm using the JDBC driver.

Mainly the problem is that the JDBC interface doesn't
provide the method begin() for a transaction object,
of course this is not a JDBC postgres interface problem.

Let me explain what happen using the JDBC interface

Client Side | Server Side
---------------------------------------------------

1) Open a connection | Connection accepted
| <- Connection Idle
2) set autocommit false | begin;
| <- Idle in transaction
3) select now(); | select now();
| <- Idle in transaction
4) commit; | commit; begin;
| <- Idle in transaction
5) select now(); | select now();
| <- Idle in transaction
6) rollback; | rollback; begin;
| <- Idle in transaction

as you can easily understand there is no window time larger enough with
a connection idle, I thin that the JDBC behaviour ( with the server I
mean ) is not really correct: if the application is waiting for a user
entry then the connection remain: idle in transaction.

This is the behaviour that I think it's better:

Client Side | Server Side
---------------------------------------------------

1) Open a connection | Connection accepted
| <- Connection Idle
2) set autocommit false | NOP
| <- Connection Idle
3) select now(); | begin; select now();
| <- Idle in transaction
4) commit; | commit;
| <- Connection Idle
5) select now(); | begin; select now();
| <- Idle in transaction
6) select now(); | select now();
| <- Idle in transaction
7) rollback; | rollback;
| <- Connection Idle

AS you can see the JDBC driver must do a begin only before the
first statement.

Am I missing something ?

Regards
Gaetano Mendola

> I'm facing a problem with the unfamous:
>
> "idle in transaction"

Why is this "infamous"? Does it cause problems somewhere? I understand it
may "look unusual" to you, but there's really no issue in that each
connection that doesn't use autocommit is always in a transaction, so that
any statements you issue are done under that transaction until you
commit/rollback, at which time a new transaction is initiated in preparation
for further statements being issued.

Is there some issue that Postgresql has problems because such transactions
are held open for a long time? I've never seen any such problem in the
years using PG. After all, maintaining the open connection is the expensive
operation, and I think the open transaction is a nit (but I certainly can be
wrong!).

David

David Wall wrote:

>>I'm facing a problem with the unfamous:
>>
>>"idle in transaction"
>
>
> Why is this "infamous"? Does it cause problems somewhere? I understand it
> may "look unusual" to you, but there's really no issue in that each
> connection that doesn't use autocommit is always in a transaction, so that
> any statements you issue are done under that transaction until you
> commit/rollback, at which time a new transaction is initiated in preparation
> for further statements being issued.
>
> Is there some issue that Postgresql has problems because such transactions
> are held open for a long time? I've never seen any such problem in the
> years using PG. After all, maintaining the open connection is the expensive
> operation, and I think the open transaction is a nit (but I certainly can be
> wrong!).

Yes a "idle in transaction" connection for example block each vacuum
full, just not to mention all the rows kidnapped by that transaction.

Again, for me the actual behave is not the right one.

Regards
Gaetano Mendola.

Gaetano Mendola wrote:

> David Wall wrote:
>
>>> I'm facing a problem with the unfamous:
>>>
>>> "idle in transaction"
>>
>>
>>
>> Why is this "infamous"? Does it cause problems somewhere? I
>> understand it
>> may "look unusual" to you, but there's really no issue in that each
>> connection that doesn't use autocommit is always in a transaction, so
>> that
>> any statements you issue are done under that transaction until you
>> commit/rollback, at which time a new transaction is initiated in
>> preparation
>> for further statements being issued.
>>
>> Is there some issue that Postgresql has problems because such
>> transactions
>> are held open for a long time? I've never seen any such problem in the
>> years using PG. After all, maintaining the open connection is the
>> expensive
>> operation, and I think the open transaction is a nit (but I certainly
>> can be
>> wrong!).
>
>
> Yes a "idle in transaction" connection for example block each vacuum
> full, just not to mention all the rows kidnapped by that transaction.

Not block sorry, but the vacuum is not able to free all the row updated
by other connections.

try to connect to your DB, do a "begin" and leave that transaction
sitting there for days...

Regards
Gaetano Mendola.

> Not block sorry, but the vacuum is not able to free all the row updated
> by other connections.

But each connection shouldn't have any updated rows that haven't been
committed or rolled back. Now, if you are running some statements against a
connection and then never calling commit/rollback, that's another thing
since you are actually tying up resources then. But since the driver just
does a 'begin' after any given commit/rollback, there shouldn't be anything
locked by that connection.

David

Gaetano Mendola wrote:

> AS you can see the JDBC driver must do a begin only before the
> first statement.
>
> Am I missing something ?

This has been something that could be improved for some time now. If we
delay the BEGIN we could also deal with transaction isolation changes
better. Overall, though, it's a pretty minor thing.

Patches are welcome..

-O

A bit off topic:
We implemented our own pooling mechanism which when the connection is in
the pool we set autocommit(false) thereby setting the state of the
connection to "idle" and when the pool hands out the connection we flip
it to autocommit(true). This made it much easier to find
connections/transactions orphaned by the application code.
Just food for thought.

On Thu, 2004-04-08 at 19:28, David Wall wrote:
> > Not block sorry, but the vacuum is not able to free all the row updated
> > by other connections.
>
> But each connection shouldn't have any updated rows that haven't been
> committed or rolled back. Now, if you are running some statements against a
> connection and then never calling commit/rollback, that's another thing
> since you are actually tying up resources then. But since the driver just
> does a 'begin' after any given commit/rollback, there shouldn't be anything
> locked by that connection.
>
> David
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
--
Warren Little
Senior Vice President
Secondary Marketing
Security Lending Wholesale, LC
www.securitylending.com
Tel: 866-369-7763
Fax: 866-849-8079

David Wall wrote:
>>Not block sorry, but the vacuum is not able to free all the row updated
>>by other connections.
>
>
> But each connection shouldn't have any updated rows that haven't been
> committed or rolled back. Now, if you are running some statements against a
> connection and then never calling commit/rollback, that's another thing
> since you are actually tying up resources then. But since the driver just
> does a 'begin' after any given commit/rollback, there shouldn't be anything
> locked by that connection.

It's not a lock issue as I understand it. It's that the presence of an
open "old" transaction means that for rows that have been updated since
that transaction started, VACUUM cannot delete the "old" version of the row.

For example:

> test=> select * from t;
> i | j
> ---+---
> 1 | 1
> 2 | 2
> 3 | 3
> (3 rows)
>
> test=> vacuum full verbose t;
> INFO: vacuuming "public.t"
> INFO: "t": found 3 removable, 3 nonremovable row versions in 1 pages
> DETAIL: 0 dead row versions cannot be removed yet. [...]

> test=> begin;
> BEGIN

On a separate connection:

> test=> update t set j=5 where i=3;
> UPDATE 1
> test=> vacuum full verbose t;
> INFO: vacuuming "public.t"
> INFO: "t": found 0 removable, 4 nonremovable row versions in 1 pages
> DETAIL: 1 dead row versions cannot be removed yet. [...]

Back on the original connection:

> test=> commit;
> COMMIT
> test=> vacuum full verbose t;
> INFO: vacuuming "public.t"
> INFO: "t": found 1 removable, 3 nonremovable row versions in 1 pages
> DETAIL: 0 dead row versions cannot be removed yet. [...]

So the open transaction prevents the old version of the row (where i=3
and j=3) from being removed.

I have a feeling this is an implementation artifact more than anything
-- as it appears that the snapshot to use for a (serializable)
transaction is not actually "taken" until the first query in a
transaction is executed, so the unremovable row in the above example is
never actually needed. But I'm not familiar with how the backend code
works so this is mostly guesswork :)

-O

> It's not a lock issue as I understand it. It's that the presence of an
> open "old" transaction means that for rows that have been updated since
> that transaction started, VACUUM cannot delete the "old" version of the
row.

Why would the "old" transaction have anything locked up if it hadn't done a
select, update, insert or delete? And if it did, then why not
commit/rollback and leave the connection in a more clean state? Two
transactions shouldn't bother one another.

I'm not sure what state information is maintained in the JDBC library, but
assuming it can maintain state, it shouldn't be hard for it to know whether
it's in autocommit mode or not, and if not, then only issue the 'begin' when
a statement is first created on the connection, and then reset that state
after a commit/rollback.

But I'm going to look at my dbs with the vacuum full verbose command and see
if there's anything stuck in my system!

David

David Wall wrote:
>>It's not a lock issue as I understand it. It's that the presence of an
>>open "old" transaction means that for rows that have been updated since
>>that transaction started, VACUUM cannot delete the "old" version of the
>
> row.
>
>
> Why would the "old" transaction have anything locked up if it hadn't done a
> select, update, insert or delete?

Empirically, an open transaction that has run no queries *does* prevent
some rows from being vacuumed. I haven't delved into the backend code to
see why exactly; asking on -hackers may be a better bet.

> I'm not sure what state information is maintained in the JDBC library, but
> assuming it can maintain state, it shouldn't be hard for it to know whether
> it's in autocommit mode or not, and if not, then only issue the 'begin' when
> a statement is first created on the connection, and then reset that state
> after a commit/rollback.

That's the plan; it just needs implementing. There are also some
interactions with changing transaction isolation levels that should be
dealt with at the same time.

This is somewhere on my todo list and I'll get to it eventually if noone
else does, but there's quite a bit of other stuff ahead of it at the
moment..

-O

> That's the plan; it just needs implementing. There are also some
> interactions with changing transaction isolation levels that should be
> dealt with at the same time.
>
> This is somewhere on my todo list and I'll get to it eventually if noone
> else does, but there's quite a bit of other stuff ahead of it at the
> moment..

Wow, thanks. Yeah, I agree about priorities. The problem doesn't bother me
at all since we've not seen any issues with PG and it's worked like a champ
for several years in multiple deployments, including being used in Fortune
500 deployments, by our office edition licensees of Signed & Secured, and to
drive our public web service at yozons.com. It's just been awesome, fast
and reliable. In fact, when we had to port Oracle, we had to "dumb down"
our application a bit because of their odd handling of BLOBs, inability to
support multiple TEXT (LONG) fields in a single table and their shorter
table and field names.

David

On Friday 09 April 2004 3:23 am, David Wall wrote:
> Wow, thanks. Yeah, I agree about priorities. The problem doesn't bother
> me at all since we've not seen any issues with PG and it's worked like a
> champ for several years in multiple deployments, including being used in
> Fortune 500 deployments, by our office edition licensees of Signed &
> Secured, and to drive our public web service at yozons.com. It's just been
> awesome, fast and reliable. In fact, when we had to port Oracle, we had to
> "dumb down" our application a bit because of their odd handling of BLOBs,
> inability to support multiple TEXT (LONG) fields in a single table and
> their shorter table and field names.

I am rather keen to develop a service porting people toPG from Oracle.
I have always got the impression that this was a non-no due to the PL side of
Oracl PL/SQL?

What can Oracle do that PG can't ?

cheers
timp

On Apr 9, 2004, at 4:58 AM, Tim Pizey wrote:

>
>
> What can Oracle do that PG can't ?
>

Bankrupt you? Make your hair fall out? Cause ulcers? The list is
endless....

;-)

> cheers
> timp
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114
www.ravensfield.com

Andrew Rawnsley wrote:
>
> On Apr 9, 2004, at 4:58 AM, Tim Pizey wrote:
>
>>
>>
>> What can Oracle do that PG can't ?
>>
>
> Bankrupt you? Make your hair fall out? Cause ulcers? The list is
> endless....
>
> ;-)

pro: OLAP, recursive queries, clustering, tablespaces, advanced queuing,
bitmap indexing, index organized tables, materialized views, database
links, nested tables, cubes, ... (just to name a few).

contra: bankrupt you, driver your dbas crazy, malloc(size_of(ram) * 1000)

Don't get me wrong. PostgreSQL is a damn good product and it supports
95% of all commonly used features.
However, it is not time yet to tell that we can do EVERYTHING oracle can
do and that Oracle is already completely obsolete. There are still areas
which cannot be covered with PostgreSQL
For a "normal" database PostgreSQL is definitely the better choice but
when working with cubes and so forth it can be damn hard. More work is
needed to make PostgreSQL support these features.

Regards,

Hans

--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/2952/30706 or +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at

Gaetano Mendola wrote:
> Gaetano Mendola wrote:
>> Yes a "idle in transaction" connection for example block each vacuum
>> full, just not to mention all the rows kidnapped by that transaction.
>
>
> Not block sorry, but the vacuum is not able to free all the row updated
> by other connections.
>

I agree that your suggested change in behavior is good (please feel free
to submit a patch), it isn't necessarily going to solve your problem.

> try to connect to your DB, do a "begin" and leave that transaction
> sitting there for days...

The problem here is that your application would allow a connection to
the database to sit around for days. Even with your suggested changes
to the driver, if a jdbc connection did a select (which would start a
transaction) and then left that connection sit arround for days you
would still have the same problem. Your suggested change to the driver
just delays the point where a connection starts, but if you allow long
lived connections to the database I contend that you will still see your
problem since some code somewhere will simply issue a select and then
hold an open idle connection for long periods of time.

--Barry

Barry Lind wrote:

> > try to connect to your DB, do a "begin" and leave that transaction
> > sitting there for days...
>
> The problem here is that your application would allow a connection to
> the database to sit around for days. Even with your suggested changes
> to the driver, if a jdbc connection did a select (which would start a
> transaction) and then left that connection sit arround for days you
> would still have the same problem. Your suggested change to the driver
> just delays the point where a connection starts, but if you allow long
> lived connections to the database I contend that you will still see your
> problem since some code somewhere will simply issue a select and then
> hold an open idle connection for long periods of time.

If you're pooling connections, while it makes sense for the pool to
commit/rollback any transaction in progress when the connection is
released by a client, it's not obvious that the pool should immediately
reset the autocommit state of the connection. But the current driver
requires setting autocommit on idle connections to avoid keeping a
transaction open.

So you can have:

get connection from pool
conn.setAutoCommit(true);
conn.setAutoCommit(false); -> begin
stmt.executeUpdate("foo"); -> foo
commit() -> commit;begin
return connection to pool
rollback() -> rollback;begin

connection is idle in the pool for the next week.

get connection from pool
conn.setAutoCommit(true); -> commit
// client uses connection

This seems to be the main case where the change in behaviour is useful.

-O

On Thu, 8 Apr 2004, Gaetano Mendola wrote:

> Hi all,
> I'm facing a problem with the unfamous:
>
> "idle in transaction"
>

I have put in transaction state tracking to the cvs version of the driver.
If you could give this some testing I'd appreciate it. Binaries are
available here:

http://www.ejurka.com/pgsql/jars/

Kris Jurka