17

I am using Postgres for one of my applications and sometimes (not very frequently) one of the connection goes into <IDLE> in transaction state and it keeps acquired lock that causes other connections to wait on these locks ultimately causing my application to hang.

Following is the output from pg_stat_activity table for that process:

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       | <IDLE> in transaction

This indicates that PID=798 is in <IDLE> in transaction state. The client process on web server is found as following using the client_port (59034) from above output.

sudo netstat -apl | grep 59034

tcp        0      0 ip-10-112-61-218.:59034 db-server:postgresql    ESTABLISHED 23843/pgbouncer

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.

This is not very frequent and I can't find any definite reproduction steps either as this only occurs on the production server.

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.

ADTC
  • 8,999
  • 5
  • 68
  • 93
Amit
  • 351
  • 1
  • 2
  • 8
  • Pleas specify what PostgreSQL version you are using. – Mark Stosberg Sep 13 '13 at 15:14
  • psql -V => psql (PostgreSQL) 9.1.8 – Amit Sep 13 '13 at 15:19
  • `psql -V` gives you the version of the `psql` client tool, not the version of the database server. You should run `select version()` to make sure. I think using the `pg_stat_statements` extension might help you in finding the root cause: http://www.postgresql.org/docs/9.1/static/pgstatstatements.html (it has been significantly enhanced in 9.2 btw) –  Sep 13 '13 at 16:16
  • 'pg_stat_statements' will not give me info on statements executed by a specific connection id (to trace statements of idle connection). In worst case, I could go for logging all sql queries in log file but that will slow down the production server. I am looking for a solution that allows me debug the hanged connection to see its trace without affecting the server performance by much. – Amit Sep 13 '13 at 17:29
  • You can attach gdb to a running client process: http://stackoverflow.com/q/2308653/15862 – Tometzky Sep 13 '13 at 19:05
  • @Tometzky Yes, but how do I see the last executed query or any other trace information using gdb for a postgres connection process? – Amit Sep 15 '13 at 17:46

1 Answers1

12

If you upgrade to 9.2 or higher, the pg_stat_activity view will show you what the most recent query executed was for idle in transaction connections.

select * from pg_stat_activity  \x\g\x

...
waiting          | f
state            | idle in transaction
query            | select count(*) from pg_class ;

You can also (even in 9.1) look in pg_locks to see what locks are being held by the idle in transaction process. If it only has locks on very commonly used objects, this might not narrow things down much, but if it was a peculiar lock that could tell you exactly where in your code to look.

If you are stuck with 9.1, you can perhaps use the debugger to get all but the first 22 characters of the query (the first 22 are overwritten by the <IDLE> in transaction\0 message). For example:

(gdb) printf "%s\n", ((MyBEEntry->st_activity)+22)
ADTC
  • 8,999
  • 5
  • 68
  • 93
jjanes
  • 37,812
  • 5
  • 27
  • 34
  • Yes, I zeroed down on the lock relation but thats a commonly used table so cant figure out much from that. Is there any other way eg. using gdb debugger? – Amit Sep 13 '13 at 18:18