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