title: Postgres - How to debug/trace 'Idle in transaction' connection
body: <p>I am using Postgres for one of my applications and sometimes (not very frequently) one of the connection goes into <code>&lt;IDLE&gt; in transaction</code> state and it keeps acquired lock that causes other connections to wait on these locks ultimately causing my application to hang. </p>
  
  <p>Following is the output from <code>pg_stat_activity</code> table for that process:</p>
  
  <pre><code>select * from pg_stat_activity
  
  24081 | db     |     798 |    16384 | db     |                  | 10.112.61.218 |                 |       59034 | 2013-09-12 23:46:05.132267+00 | 2013-09-12 23:47:31.763084+00 | 2013-09-12 23:47:31.763534+00 | f       | &lt;IDLE&gt; in transaction
  </code></pre>
  
  <p>This indicates that <code>PID=798</code> is in <code>&lt;IDLE&gt; in transaction</code> state. The client process on web server is found as following using the <code>client_port</code> (<code>59034</code>) from above output.</p>
  
  <pre><code>sudo netstat -apl | grep 59034
  
  tcp        0      0 ip-10-112-61-218.:59034 db-server:postgresql    ESTABLISHED 23843/pgbouncer
  </code></pre>
  
  <p>I know that something is wrong in my application code (I killed one of the running application cron and it freed the locks) that is causing the connection to hang, but I am not able to trace it.</p>
  
  <p>This is not very frequent and I can't find any definite reproduction steps either as this only occurs on the production server.</p>
  
  <p>I would like to get inputs on how to trace such idle connection, e.g. getting last executed query or some kind of trace-back to identify which part of code is causing this issue.</p>