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.

Improve this question
6
  • Pleas specify what PostgreSQL version you are using. Commented Sep 13, 2013 at 15:14
  • psql -V => psql (PostgreSQL) 9.1.8 Commented Sep 13, 2013 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: postgresql.org/docs/9.1/static/pgstatstatements.html (it has been significantly enhanced in 9.2 btw) Commented Sep 13, 2013 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. Commented Sep 13, 2013 at 17:29
  • You can attach gdb to a running client process: stackoverflow.com/q/2308653/15862 Commented Sep 13, 2013 at 19:05

1 Answer 1

Reset to default
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)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.