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