This sure is a desirable feature. I have seen quite a few instances, where the app is in 'IDLE in Transaction' state, and we are left with the only choice of killing such processes from OS. (Remember pg_cancel_backend() does not work for sessions in IDLE or IDLE in transaction state)
Also, it should be introduced as a new column, rather than tacking on the existing string. Although this column will be of little use in cases where current query is visible, but having a separate column looks like a cleaner and simpler implementation. Best regards, 2009/3/25 Tatsuhito Kasahara <kasahara.tatsuh...@oss.ntt.co.jp> > Hi. > > Now, we can check the running query string by > pg_stat_activity.current_query. > If we can also check previous query_string of idle-in-transaction, > it is useful for analysis of long transaction problem. > > Long-transaction is a trouble, because it prevents defragmentation of HOT > and VACUUM. > And long-transaction tends to be it in a state of "idle in transaction". > (BEGIN -> SOME SQL -> .... (long-transactin) ....) > > So, I sometimes want to know what query (main cause) was done before > transaction which have been practiced for a long time. > > I think that we are glad when we can confirm it in the following form. > # We will be able to use debug_query_string in postgres.c for this purpose. > > ================================================================= > =# SELECT current_query FROM pg_stat_activity > WHERE procpid <> pg_backend_pid(); > > current_query > --------------------------------------------------------------- > <IDLE> in transaction [prev]: SELECT * FROM pg_class limit 1; > > ================================================================= > > Thoughts? > > Best regards. > > -- > Tatsuhito Kasahara > kasahara.tatsuh...@oss.ntt.co.jp > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- gurjeet[.sin...@enterprisedb.com singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
