Interesting... Looking at your log I see this:
15:33:08.267 (2) FE=> Parse(stmt=S_1,query="BEGIN",oids={})
.....
15:33:08.268 (2) FE=> Parse(stmt=null,query="SELECT ....
....
15:33:28.461 (2) FE=> Parse(stmt=S_2,query="COMMIT",oids={})
So transaction is clearly committed. But of course connection is not
closed. It is returned to the pool instead. Still looks like a driver
bug to me.
Andrus
On Apr 24, 2007, at 5:03 PM, Oilid Adsi wrote:
> Hi again,
>
>> -----Ursprüngliche Nachricht-----
>> Von: Andrus Adamchik [mailto:andru..bjectstyle.org]
>> Gesendet: Dienstag, 24. April 2007 12:23
>> An: use..ayenne.apache.org
>> Betreff: Re: postgres, idle in transaction
>>
>>
>> On Apr 24, 2007, at 12:58 PM, Oilid Adsi wrote:
>>
>>> Normally I thought we can do the workaround with committing every
>>> query (method commitChanges in the DataContext) or setting the JDBC-
>>> parameter defaultAutoCommit="true". But both of these workarounds
>>> didn't work properly.
>>
>> I am sure they did not. I was surprised when Peter reported that
>> 'commitChanges' worked as a workaround. This shouldn't make any
>> difference.
>>
>>> Postgres-JDBC-driver 8.2-504.jdbc3
>>
>> Worth checking a different version as well (I've see strangest cross-
>> version issues with the driver in the past).
>>
>
> The postgres mailinglist is sure that this problem/bug was fixed
> (see attachment). I also switched to the newest jdbc-version:
> PostgreSQL 8.3devel JDBC3 with SSL (build 600).
> << Re JDBC idle in transaction problem.txt >>
>
>>
>>>> Also could you possibly switch the DataSource to DBCP [2] and
>>>> see if
>>>> that DBCP DataSource does the right thing?
>>> We will do this switch and give feedback.
>>
>> Please do. This will be an indication of whether we need to fix our
>> connection pool or not.
>>
>
> Same behaviour and still the same problem with DBCP DataSource ;-(
> Sometimes the transaction will be committed sometimes not:
> << postgres_debug_transaction.txt >>
>
>>
>>> Why cayenne uses a transaction (BEGIN - COMMIT) for performing a
>>> select-query? Is this the useful way as described on this link:
>>> http://www.ashtech.net/~syntax/blog/archives/56-Hibernate-and-
>>> PostgreSQL-Require-Transactions.html
>>
>> Main reason is consistency. There can be select queries that are not
>> just select (e.g. stored procedure queries that both select and
>> update). So Cayenne handles all queries the same way, instead of
>> trying to analyze each query coming down the pipe.
>>
>> Andrus
>>
>
> <Re JDBC idle in transaction problem.txt>
> <postgres_debug_transaction.txt>
This archive was generated by hypermail 2.0.0 : Tue Apr 24 2007 - 10:17:51 EDT