Re: Idle in transaction help

Thanks scott, but I wrote a cgi to combine all of the process info and allow me to kill errant queries. So I know how to track down the pid. Thanks for trying to help though. :-)

----- Original Message -----
From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: Scot Kreienkamp
Cc: pgsql-general(at)postgresql(dot)org <pgsql-general(at)postgresql(dot)org>
Sent: Fri Jul 10 18:34:14 2009
Subject: Re: [GENERAL] Idle in transaction help

On Fri, Jul 10, 2009 at 2:05 PM, Scot Kreienkamp<SKreien(at)la-z-boy(dot)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.