Re: Idle in transaction help

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