|
I noticed 100s of waiting sessions in my production DB yesterday. Upon troubleshooting, I found an insert statement (idle in transaction) that was blocking. This is what I saw in pg_stat_activity: site=# select * from pg_stat_activity where pid=62334; -[ RECORD 1 ]----+------------------------------------------------------------------------------------------------------------------------ datid | DBID datname | DBNAME pid | 62334 usesysid | 44490 usename | USERNAME application_name | APPNAME client_addr | CLIENTIP client_hostname | HOSTNAME client_port | 51987 backend_start | 2015-04-06 20:55:07.921089-07 xact_start | 2015-04-06 21:16:26.820822-07 query_start | 2015-04-06 21:16:26.834017-07 state_change | 2015-04-06 21:16:26.834144-07 waiting | f state | idle in transaction query | INSERT into distributed_events (type, action, id, properties) VALUES ($1, $2, $3, $4) RETURNING "distributed_event_id" I found this blocking session and killed it and the locks cleared within a second. After I killed it, this is all I saw in the postgresql.log: Apr 7 14:26:50 site-db01a postgres[62334]: [11-1] app=APPNAME,user=USERNAME,db=DBNAME,ip=CLIENTIP FATAL: terminating connection due to administrator command When I sent this information to the dev team, they came back saying that there was no error in the application logs and asked for the value of the bind variables to help them troubleshoot further. My question: Is there a way to find out the value of bind variables executed in a current session (show them in pg_stat_activity)? Or to log it in the postgresql.log or a table after the session was killed? I know that bind variables of
completed queries will be logged in postgresql.log. But what about the ones that were killed or terminated for any reason? Thank you for your help Ramya |
