Von: Dave Cramer [pg@fastcrypt.com]
Gesendet: Dienstag, 24. April 2007 14:38
An: Oilid Adsi
Cc: pgsql-jdbc@postgresql.org
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
> er@cf5006, 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 majordomo@postgresql.org so that 
> your
>        message can get through to the mailing list cleanly