Re: display previous query string of idle-in-transaction

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(dot)tatsuhito(at)oss(dot)ntt(dot)co(dot)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(dot)tatsuhito(at)oss(dot)ntt(dot)co(dot)jp
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device