Hi John, It is Java. I asked our programmers to check on the JDBC version as I had seen that on the list previously. It is using postgresql-8.2-504. Is that one of the problem versions? I had thought it was new enough that it would not be subject to that problem. The unexplained part is why are there locks acquired, sometimes on the row level, prior to the connection going to idle in transaction status? That makes me think it's not the JDBC driver. Thanks, Scot Kreienkamp -----Original Message----- From: John R Pierce [mailto:pierce@xxxxxxxxxxxx] Sent: Friday, July 10, 2009 4:21 PM To: Scot Kreienkamp Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: Idle in transaction help Scot Kreienkamp wrote: > > Hi everyone, > > I need some help with tracking down idle in transaction problems. We > have a custom application that is leaving queries in idle in > transaction status for unknown reasons. The developers are working on > ways to track it down, but right now the options on their end are > limited and it will be at least 1-2 months until they have something > working. I am trying to track down the problem from the PG end in the > meantime. Is there any way to tell what query is hanging in idle in > transaction status? Or what the current or previous query was/is, > since idle in transaction doesn't tell me anything? I'm kind of at a > loss on what if anything I can do from the database end to help (read > push) the programmers to find and fix this problem. > there is no active query, thats why its idle. they did a "BEGIN" to start a transaction, then left the connection idle. is this software, by any chance, Java based? older versions of the Postgres JDBC module had a nasty habit of doing this, as JDBC autogenerates the BEGIN if its not in autocommit mode. the older version would generate the begin immediately after a COMMIT or ROLLBACK to prepare for the next transaction, and if the app simply stopped using the connection, it was left IDLE IN TRANSACTION. The updated version postpones the BEGIN until you issue your first query. if you enable statement logging and set up a log prefix to show the Process ID (and I usually prefix with a timestamp, database name and other useful stuff), then you can grep the logs for the PID of the IDLE IN TRANSACTION process. Note logging all statements is pretty CPU and disk intensive, so likely will impact your system performance, so should only be done for debug purposes. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
