Thread: Re: [Password?]

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.

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@hogranch.com]
Sent: Friday, July 10, 2009 4:21 PM
To: Scot Kreienkamp
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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.

Scot Kreienkamp wrote:
> 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.
>

well, the current JDBC for 8.2 is build 510.  see
http://jdbc.postgresql.org/download.html

It does appear the idle-in-transaction bug I'm remembering is pretty
old...  it was fixed in Version 8.0-dev302 (2004-06-15), heh.  your
version is from 2006-12-01.     I would still consider upgrading, there
have been a lot of fixes and enhancements between 504 and 510...  See
http://jdbc.postgresql.org/changes.html  for a revision history.


Remember, if you have autocommit OFF, then even read-only (select-only)
connections need commits, or they stay in transaction.

On Fri, Jul 10, 2009 at 2:05 PM, Scot Kreienkamp<SKreien@la-z-boy.com> 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.
>
>
>
> My first priority is helping them find and fix the problem if I can.  My
> second priority is finding an automated way to deal with the idle in
> transactions as they are locking tables and rows, causing other transactions
> to hang also.  None of the timeouts appear to affect idle in transactions.

Assuming that tracking down the process that's connected might help,
you can use pg_stat_activity to find the port that the client is
connecting from, then on the client machine, use lsof to hunt down the
process that is connecting via that port.

For instance, I connect from my laptop with two connections.  One I do
a begin; in and in the other I look it up like so:

select * from pg_stat_activity where current_query ilike
'%idle%trans%' and current_query not ilike 'select%';
 datid | datname  | procpid | usesysid | usename  |     current_query
   | waiting |          xact_start           |          query_start
      |         backend_start         | client_addr  | client_port

-------+----------+---------+----------+----------+-----------------------+---------+-------------------------------+-------------------------------+-------------------------------+--------------+-------------
 11511 | postgres |   24893 |    16413 | smarlowe | <IDLE> in
transaction | f       | 2009-07-10 16:20:15.056385-06 | 2009-07-10
16:20:15.056385-06 | 2009-07-10 15:27:48.944738-06 | 192.168.0.74 |
   48727

The client port is 48727.  Now, on my laptop I can do:

sudo lsof |grep 48727 and I have this line in there:

psql      27964   smarlowe    3u     IPv4            1114765
      TCP steamboat:48727->192.168.0.247:postgresql (ESTABLISHED)

Note that 27964 is the pid of the psql command that's connected to the
server.  Hope that helps a little.

On Jul 10, 2009, at 3:34 PM, Scott Marlowe wrote:

> Assuming that tracking down the process that's connected might help,
> you can use pg_stat_activity to find the port that the client is
> connecting from, then on the client machine, use lsof to hunt down the
> process that is connecting via that port.
>
> For instance, I connect from my laptop with two connections.  One I do
> a begin; in and in the other I look it up like so:
>
> select * from pg_stat_activity where current_query ilike
> '%idle%trans%' and current_query not ilike 'select%';
> datid | datname  | procpid | usesysid | usename  |     current_query
>   | waiting |          xact_start           |          query_start
>      |         backend_start         | client_addr  | client_port
> -------+----------+---------+----------+----------
> +-----------------------+---------+-------------------------------
> +-------------------------------+-------------------------------
> +--------------+-------------
> 11511 | postgres |   24893 |    16413 | smarlowe | <IDLE> in
> transaction | f       | 2009-07-10 16:20:15.056385-06 | 2009-07-10
> 16:20:15.056385-06 | 2009-07-10 15:27:48.944738-06 | 192.168.0.74 |
>   48727
>
> The client port is 48727.  Now, on my laptop I can do:
>
> sudo lsof |grep 48727 and I have this line in there:
>
> psql      27964   smarlowe    3u     IPv4            1114765
>      TCP steamboat:48727->192.168.0.247:postgresql (ESTABLISHED)

Just a little tidbit for that: you can have lsof tell you what's got
that port open directly, no need for grep:

lsof -i tcp:48727

that way you keep the column headers in the output.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k