Hi again,
> -----Ursprüngliche Nachricht-----
> Von: Andrus Adamchik [mailto:[EMAIL PROTECTED]
> Gesendet: Dienstag, 24. April 2007 12:23
> An: [email protected]
> 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
>
Von: Dave Cramer [EMAIL PROTECTED]
Gesendet: Dienstag, 24. April 2007 14:38
An: Oilid Adsi
Cc: [EMAIL PROTECTED]
Betreff: Re: [JDBC] idle in transaction problem
Hi,
Are you sure you are closing your connections properly ?
The bug has been fixed.
Dave
On 24-Apr-07, at 7:56 AM, Oilid Adsi wrote:
> Hi all,
>
> i have a problem with some connections in "idle in transaction" state,
> wich indicates some transactions not beeing properly closed.
>
> I followed the mailing list and read that this bug should be fixed in
> the newer jdbc driver versions. But i tried out several newer driver
> versions.
> And now I used the newest one 8.3devel (build 600) and still have this
> problem.
>
> What's abound this information from the year in 2004?
> http://jdbc.postgresql.org/changes.html#version_dev302
>
> "Track transaction status and only issue a BEGIN command on the first
> statement executed, not immediately after the previous commit or
> rollback. This should help the long standing, but recently very
> unpopular "idle in transaction" behavior. (jurka)"
>
> The postgres server runs with version 8.1.4
>
> The postgres jdbc debugging shows that the transaction will use BEGIN
> but no COMMIT:
>
> 12:59:53.135 (4) PostgreSQL 8.3devel JDBC3 with SSL (build 600)
> 12:59:53.136 (4) Trying to establish a protocol version 3 connection
> to
> 194.97.110.106:5432
> 12:59:53.137 (4) FE=> StartupPacket(user=whitelabel_service,
> database=vitrado, client_encoding=UNICODE, DateStyle=ISO)
> 12:59:53.138 (4) <=BE AuthenticationReqMD5(salt=0caa6b40)
> 12:59:53.138 (4) FE=>
> Password(md5digest=md55f0de5c331bb8e4658422b78683fc50d)
> 12:59:53.141 (4) <=BE AuthenticationOk
> 12:59:53.141 (4) <=BE ParameterStatus(client_encoding = UNICODE)
> 12:59:53.141 (4) <=BE ParameterStatus(DateStyle = ISO, DMY)
> 12:59:53.141 (4) <=BE ParameterStatus(integer_datetimes = on)
> 12:59:53.141 (4) <=BE ParameterStatus(is_superuser = off)
> 12:59:53.141 (4) <=BE ParameterStatus(server_encoding = UTF8)
> 12:59:53.141 (4) <=BE ParameterStatus(server_version = 8.1.4)
> 12:59:53.141 (4) <=BE ParameterStatus(session_authorization =
> whitelabel_service)
> 12:59:53.141 (4) <=BE ParameterStatus(standard_conforming_strings =
> off)
> 12:59:53.142 (4) <=BE ParameterStatus(TimeZone = Europe/Berlin)
> 12:59:53.142 (4) <=BE BackendKeyData(pid=4123,ckey=1807219524)
> 12:59:53.142 (4) <=BE ReadyForQuery(I)
> 12:59:53.142 (4) compatible = 8.3
> 12:59:53.142 (4) loglevel = 2
> 12:59:53.142 (4) prepare threshold = 5
> 12:59:53.143 (4) simple execute,
> handler=org.postgresql.jdbc2.AbstractJdbc2Statement
> $StatementResultHandl
> [EMAIL PROTECTED], maxRows=0, fetchSize=0, flags=1
> 12:59:53.143 (4) FE=> Parse(stmt=S_1,query="BEGIN",oids={})
> 12:59:53.143 (4) FE=> Bind(stmt=S_1,portal=null)
> 12:59:53.143 (4) FE=> Execute(portal=null,limit=0)
> 12:59:53.143 (4) FE=> Parse(stmt=null,query="SELECT t0.angelegt,
> t0.bezeichnung, t0.nochange_allowed, t0.partner_aktiv, t0.partner_id,
> t0.produktanbieter_akt iv, t0.produkte_id, t0.werbearten_id, t0.id
> FROM public.tracking t0 LIMIT 2",oids={})
> 12:59:53.143 (4) FE=> Bind(stmt=null,portal=null)
> 12:59:53.143 (4) FE=> Describe(portal=null)
> 12:59:53.143 (4) FE=> Execute(portal=null,limit=0)
> 12:59:53.143 (4) FE=> Sync
> 12:59:53.148 (4) <=BE ParseComplete [S_1]
> 12:59:53.148 (4) <=BE BindComplete [null]
> 12:59:53.148 (4) <=BE CommandStatus(BEGIN)
> 12:59:53.148 (4) <=BE ParseComplete [null]
> 12:59:53.148 (4) <=BE BindComplete [null]
> 12:59:53.148 (4) <=BE RowDescription(9)
> 12:59:53.148 (4) <=BE DataRow
> 12:59:53.148 (4) <=BE DataRow
> 12:59:53.148 (4) <=BE CommandStatus(SELECT)
> 12:59:53.149 (4) <=BE ReadyForQuery(T)
>
> For me this is still a bug, isn't it?
>
> Best regards
> Oilid
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED] so that
> your
> message can get through to the mailing list cleanly
15:31:05.990 (1) PostgreSQL 8.3devel JDBC3 with SSL (build 600)
15:31:05.998 (1) Trying to establish a protocol version 3 connection to
194.97.110.106:5432
15:31:06.007 (1) FE=> StartupPacket(user=whitelabel_service, database=vitrado,
client_encoding=UNICODE, DateStyle=ISO)
15:31:06.009 (1) <=BE AuthenticationReqMD5(salt=53855318)
15:31:06.010 (1) FE=> Password(md5digest=md5dc90b78c11e3b15462bffbaa05687497)
15:31:06.014 (1) <=BE AuthenticationOk
15:31:06.029 (1) <=BE ParameterStatus(client_encoding = UNICODE)
15:31:06.029 (1) <=BE ParameterStatus(DateStyle = ISO, DMY)
15:31:06.029 (1) <=BE ParameterStatus(integer_datetimes = on)
15:31:06.029 (1) <=BE ParameterStatus(is_superuser = off)
15:31:06.029 (1) <=BE ParameterStatus(server_encoding = UTF8)
15:31:06.029 (1) <=BE ParameterStatus(server_version = 8.1.4)
15:31:06.029 (1) <=BE ParameterStatus(session_authorization =
whitelabel_service)
15:31:06.030 (1) <=BE ParameterStatus(standard_conforming_strings = off)
15:31:06.030 (1) <=BE ParameterStatus(TimeZone = Europe/Berlin)
15:31:06.030 (1) <=BE BackendKeyData(pid=5503,ckey=1736595556)
15:31:06.030 (1) <=BE ReadyForQuery(I)
15:31:06.030 (1) compatible = 8.3
15:31:06.030 (1) loglevel = 2
15:31:06.030 (1) prepare threshold = 5
getConnection returning driver[className=org.postgresql.Driver,[EMAIL PROTECTED]
15:31:06.075 (1) simple execute, [EMAIL PROTECTED], maxRows=0, fetchSize=0,
flags=17
15:31:06.075 (1) FE=> Parse(stmt=null,query="SHOW TRANSACTION ISOLATION
LEVEL",oids={})
15:31:06.075 (1) FE=> Bind(stmt=null,portal=null)
15:31:06.075 (1) FE=> Describe(portal=null)
15:31:06.075 (1) FE=> Execute(portal=null,limit=0)
15:31:06.075 (1) FE=> Sync
15:31:06.076 (1) <=BE ParseComplete [null]
15:31:06.076 (1) <=BE BindComplete [null]
15:31:06.077 (1) <=BE RowDescription(1)
15:31:06.078 (1) <=BE DataRow
15:31:06.078 (1) <=BE CommandStatus(SHOW)
15:31:06.093 (1) <=BE ReadyForQuery(I)
15:31:06.093 (1) simple execute, [EMAIL PROTECTED], maxRows=0, fetchSize=0,
flags=23
15:31:06.093 (1) FE=> Parse(stmt=null,query="SET SESSION CHARACTERISTICS AS
TRANSACTION ISOLATION LEVEL SERIALIZABLE",oids={})
15:31:06.093 (1) FE=> Bind(stmt=null,portal=null)
15:31:06.094 (1) FE=> Execute(portal=null,limit=1)
15:31:06.094 (1) FE=> Sync
15:31:06.096 (1) <=BE ParseComplete [null]
15:31:06.096 (1) <=BE BindComplete [null]
15:31:06.096 (1) <=BE CommandStatus(SET)
15:31:06.096 (1) <=BE ReadyForQuery(I)
15:31:06.135 (1) simple execute, [EMAIL PROTECTED], maxRows=0, fetchSize=0,
flags=1
15:31:06.136 (1) FE=> Parse(stmt=S_1,query="BEGIN",oids={})
15:31:06.136 (1) FE=> Bind(stmt=S_1,portal=null)
15:31:06.136 (1) FE=> Execute(portal=null,limit=0)
15:31:06.137 (1) FE=> Parse(stmt=null,query="SELECT t0.angelegt,
t0.bezeichnung, t0.nochange_allowed, t0.partner_aktiv, t0.partner_id,
t0.produktanbieter_akt
iv, t0.produkte_id, t0.werbearten_id, t0.id FROM public.tracking t0 LIMIT
2",oids={})
15:31:06.137 (1) FE=> Bind(stmt=null,portal=null)
15:31:06.137 (1) FE=> Describe(portal=null)
15:31:06.137 (1) FE=> Execute(portal=null,limit=0)
15:31:06.137 (1) FE=> Sync
15:31:06.141 (1) <=BE ParseComplete [S_1]
15:31:06.141 (1) <=BE BindComplete [null]
15:31:06.141 (1) <=BE CommandStatus(BEGIN)
15:31:06.142 (1) <=BE ParseComplete [null]
15:31:06.142 (1) <=BE BindComplete [null]
15:31:06.142 (1) <=BE RowDescription(9)
15:31:06.142 (1) <=BE DataRow
15:31:06.142 (1) <=BE DataRow
15:31:06.142 (1) <=BE CommandStatus(SELECT)
15:31:06.142 (1) <=BE ReadyForQuery(T)
15:33:08.244 (2) PostgreSQL 8.3devel JDBC3 with SSL (build 600)
15:33:08.244 (2) Trying to establish a protocol version 3 connection to
194.97.110.106:5432
15:33:08.245 (2) FE=> StartupPacket(user=whitelabel_service, database=vitrado,
client_encoding=UNICODE, DateStyle=ISO)
15:33:08.248 (2) <=BE AuthenticationReqMD5(salt=5578a668)
15:33:08.248 (2) FE=> Password(md5digest=md543adaaf299bbf604a883c13f60adb698)
15:33:08.255 (2) <=BE AuthenticationOk
15:33:08.256 (2) <=BE ParameterStatus(client_encoding = UNICODE)
15:33:08.256 (2) <=BE ParameterStatus(DateStyle = ISO, DMY)
15:33:08.256 (2) <=BE ParameterStatus(integer_datetimes = on)
15:33:08.256 (2) <=BE ParameterStatus(is_superuser = off)
15:33:08.256 (2) <=BE ParameterStatus(server_encoding = UTF8)
15:33:08.256 (2) <=BE ParameterStatus(server_version = 8.1.4)
15:33:08.256 (2) <=BE ParameterStatus(session_authorization =
whitelabel_service)
15:33:08.257 (2) <=BE ParameterStatus(standard_conforming_strings = off)
15:33:08.257 (2) <=BE ParameterStatus(TimeZone = Europe/Berlin)
15:33:08.257 (2) <=BE BackendKeyData(pid=5850,ckey=143834877)
15:33:08.257 (2) <=BE ReadyForQuery(I)
15:33:08.257 (2) compatible = 8.3
15:33:08.257 (2) loglevel = 2
15:33:08.257 (2) prepare threshold = 5
getConnection returning driver[className=org.postgresql.Driver,[EMAIL PROTECTED]
15:33:08.258 (2) simple execute, [EMAIL PROTECTED], maxRows=0, fetchSize=0,
flags=17
15:33:08.258 (2) FE=> Parse(stmt=null,query="SHOW TRANSACTION ISOLATION
LEVEL",oids={})
15:33:08.258 (2) FE=> Bind(stmt=null,portal=null)
15:33:08.258 (2) FE=> Describe(portal=null)
15:33:08.258 (2) FE=> Execute(portal=null,limit=0)
15:33:08.258 (2) FE=> Sync
15:33:08.259 (2) <=BE ParseComplete [null]
15:33:08.259 (2) <=BE BindComplete [null]
15:33:08.259 (2) <=BE RowDescription(1)
15:33:08.260 (2) <=BE DataRow
15:33:08.260 (2) <=BE CommandStatus(SHOW)
15:33:08.260 (2) <=BE ReadyForQuery(I)
15:33:08.260 (2) simple execute, [EMAIL PROTECTED], maxRows=0, fetchSize=0,
flags=23
15:33:08.260 (2) FE=> Parse(stmt=null,query="SET SESSION CHARACTERISTICS AS
TRANSACTION ISOLATION LEVEL SERIALIZABLE",oids={})
15:33:08.260 (2) FE=> Bind(stmt=null,portal=null)
15:33:08.260 (2) FE=> Execute(portal=null,limit=1)
15:33:08.260 (2) FE=> Sync
15:33:08.261 (2) <=BE ParseComplete [null]
15:33:08.261 (2) <=BE BindComplete [null]
15:33:08.261 (2) <=BE CommandStatus(SET)
15:33:08.261 (2) <=BE ReadyForQuery(I)
15:33:08.267 (2) simple execute, [EMAIL PROTECTED], maxRows=0, fetchSize=0,
flags=1
15:33:08.267 (2) FE=> Parse(stmt=S_1,query="BEGIN",oids={})
15:33:08.268 (2) FE=> Bind(stmt=S_1,portal=null)
15:33:08.268 (2) FE=> Execute(portal=null,limit=0)
15:33:08.268 (2) FE=> Parse(stmt=null,query="SELECT wm.views, wm.clicks, datum
FROM (
SELECT sum(prepare.views) AS views, sum(prepare.clicks) AS clicks,
prepare.datum AS datum FROM (
SELECT tracking_id, summe_views AS views, summe_clicks AS clicks,
datum FROM statistik_partner_tag WHERE partner_id = 783028 AND
(werbearten_id = 3 OR 0 = 3) AND date_trunc('MONTH', datum) =
date_trunc('MONTH', 'Sat Sep 30 01:00:00 CEST 2006'::date)
) AS prepare GROUP BY prepare.datum
) AS wm ORDER BY datum",oids={})
15:33:08.268 (2) FE=> Bind(stmt=null,portal=null)
15:33:08.268 (2) FE=> Describe(portal=null)
15:33:08.268 (2) FE=> Execute(portal=null,limit=0)
15:33:08.268 (2) FE=> Sync
15:33:28.451 (2) <=BE ParseComplete [S_1]
15:33:28.451 (2) <=BE BindComplete [null]
15:33:28.451 (2) <=BE CommandStatus(BEGIN)
15:33:28.452 (2) <=BE ParseComplete [null]
15:33:28.452 (2) <=BE BindComplete [null]
15:33:28.452 (2) <=BE RowDescription(3)
15:33:28.452 (2) <=BE DataRow
15:33:28.452 (2) <=BE DataRow
15:33:28.452 (2) <=BE DataRow
15:33:28.452 (2) <=BE DataRow
15:33:28.452 (2) <=BE DataRow
15:33:28.452 (2) <=BE DataRow
15:33:28.452 (2) <=BE DataRow
15:33:28.452 (2) <=BE DataRow
15:33:28.452 (2) <=BE DataRow
15:33:28.452 (2) <=BE DataRow
15:33:28.453 (2) <=BE DataRow
15:33:28.453 (2) <=BE DataRow
15:33:28.453 (2) <=BE DataRow
15:33:28.453 (2) <=BE DataRow
15:33:28.453 (2) <=BE DataRow
15:33:28.453 (2) <=BE DataRow
15:33:28.453 (2) <=BE DataRow
15:33:28.453 (2) <=BE DataRow
15:33:28.453 (2) <=BE DataRow
15:33:28.453 (2) <=BE DataRow
15:33:28.453 (2) <=BE DataRow
15:33:28.453 (2) <=BE DataRow
15:33:28.453 (2) <=BE DataRow
15:33:28.453 (2) <=BE DataRow
15:33:28.454 (2) <=BE DataRow
15:33:28.454 (2) <=BE DataRow
15:33:28.454 (2) <=BE DataRow
15:33:28.454 (2) <=BE DataRow
15:33:28.454 (2) <=BE DataRow
15:33:28.454 (2) <=BE DataRow
15:33:28.454 (2) <=BE CommandStatus(SELECT)
15:33:28.454 (2) <=BE ReadyForQuery(T)
15:33:28.461 (2) simple execute, [EMAIL PROTECTED], maxRows=0, fetchSize=0,
flags=22
15:33:28.461 (2) FE=> Parse(stmt=S_2,query="COMMIT",oids={})
15:33:28.461 (2) FE=> Bind(stmt=S_2,portal=null)
15:33:28.461 (2) FE=> Execute(portal=null,limit=1)
15:33:28.461 (2) FE=> Sync
15:33:28.462 (2) <=BE ParseComplete [S_2]
15:33:28.462 (2) <=BE BindComplete [null]
15:33:28.462 (2) <=BE CommandStatus(COMMIT)
15:33:28.462 (2) <=BE ReadyForQuery(I)
