Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
Received-SPF: pass (herse.apache.org: local policy)
Mime-Version: 1.0 (Apple Message framework v752.3)
In-Reply-To: 
 <B4D30753DE59F440A8AB02C5171F434E018D02FE@FNHH-SVMEXDB003.Freenet-AG.de>
References: 
 <B4D30753DE59F440A8AB02C5171F434E018D02FE@FNHH-SVMEXDB003.Freenet-AG.de>
Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed
Message-Id: <D878CE30-6B29-4329-8573-DD20C7E0261B@objectstyle.org>
Content-Transfer-Encoding: quoted-printable
From: Andrus Adamchik <andrus@objectstyle.org>
Subject: Re: AW: postgres, idle in transaction
Date: Tue, 24 Apr 2007 17:17:12 +0300
To: user@cayenne.apache.org

Interesting... Looking at your log I see this:

15:33:08.267 (2)  FE=3D> Parse(stmt=3DS_1,query=3D"BEGIN",oids=3D{})
.....
15:33:08.268 (2)  FE=3D> Parse(stmt=3Dnull,query=3D"SELECT ....
....
15:33:28.461 (2)  FE=3D> Parse(stmt=3DS_2,query=3D"COMMIT",oids=3D{})


So transaction is clearly committed. But of course connection is not =20
closed. It is returned to the pool instead. Still looks like a driver =20=

bug to me.

Andrus


On Apr 24, 2007, at 5:03 PM, Oilid Adsi wrote:

> Hi again,
>
>> -----Urspr=FCngliche Nachricht-----
>> Von: Andrus Adamchik [mailto:andrus@objectstyle.org]
>> Gesendet: Dienstag, 24. April 2007 12:23
>> An: user@cayenne.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=3D"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 =20
> (see attachment). I also switched to the newest jdbc-version: =20
> 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 =20
>>>> 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>