Postgres Pro
Downloads
Home   >   mailing lists

Thread: idle in transaction problem

idle in transaction problem

From
"Oilid Adsi"
Date:
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

Re: idle in transaction problem

From
Heikki Linnakangas
Date:
Oilid Adsi wrote:
> The postgres jdbc debugging shows that the transaction will use BEGIN
> but no COMMIT:

What does your application look like? You're not by calling BEGIN in
your application, are you? Are you calling Connection.commit when you're
done with the query?

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: idle in transaction problem

From
Dave Cramer
Date:
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

Re: idle in transaction problem

From
"Oilid Adsi"
Date:
Symantec Mail Security replaced Message Body with this text message.  The original file was unscannable and was
quarantined.

ID:FNHH-SVMEXFE002::SYQ1dc46acaa
Attachment

Re: idle in transaction problem

From
Dave Cramer
Date:
Please send us the code without the debug messages.

 From what I could tell you aren't closing the connection or
committing the transaction.

Dave
On 24-Apr-07, at 10:19 AM, Oilid Adsi wrote:

> Symantec Mail Security replaced Message Body with this text
> message.  The original file was unscannable and was quarantined.
>
> ID:FNHH-SVMEXFE002::SYQ1dc46acaa
> <postgres_debug_transaction.txt>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq

Re: idle in transaction problem

From
"Oilid Adsi"
Date:
Hi Dave,

in which jdbc-postgres-version was this bug fixed?

Thanks, greetings
Oilid

> -----Ursprüngliche Nachricht-----
> Von: Dave Cramer [mailto: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

Re: idle in transaction problem

From
Heikki Linnakangas
Date:
Oilid Adsi wrote:
> in which jdbc-postgres-version was this bug fixed?

In version dev302, as it says in the history of changes page you quoted.

Please describe what your application does if you want us to help you.
Do you call Connection.commit after issuing the query?

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com