Re: display previous query string of idle-in-transaction - Mailing list pgsql-hackers

This sure is a desirable feature. I have seen quite a few instances, where the app is in 'IDLE in Transaction' state,
andwe are left with the only choice of killing such processes from OS. (Remember pg_cancel_backend() does not work for
sessionsin IDLE or IDLE in transaction state)<br /><br />Also, it should be introduced as a new column, rather than
tackingon the existing string. Although this column will be of little use in cases where current query is visible, but
havinga separate column looks like a cleaner and simpler implementation.<br /><br />Best regards,<br /><br /><div
class="gmail_quote">2009/3/25Tatsuhito Kasahara <span dir="ltr"><<a
href="mailto:kasahara.tatsuhito@oss.ntt.co.jp">kasahara.tatsuhito@oss.ntt.co.jp</a>></span><br/><blockquote
class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Hi.<br/><br /> Now, we can check the running query string by pg_stat_activity.current_query.<br /> If we can also check
previousquery_string of idle-in-transaction,<br /> it is useful for analysis of long transaction problem.<br /><br />
Long-transactionis a trouble, because it prevents defragmentation of HOT and VACUUM.<br /> And long-transaction tends
tobe it in a state of "idle in transaction".<br /> (BEGIN -> SOME SQL -> .... (long-transactin) ....)<br /><br />
So,I sometimes want to know what query (main cause) was done before<br /> transaction which have been practiced for a
longtime.<br /><br /> I think that we are glad when we can confirm it in the following form.<br /> # We will be able to
usedebug_query_string in postgres.c for this purpose.<br /><br />
=================================================================<br/> =# SELECT current_query FROM pg_stat_activity<br
/>  WHERE procpid <> pg_backend_pid();<br /><br />                         current_query<br />
---------------------------------------------------------------<br/>  <IDLE> in transaction [prev]: SELECT * FROM
pg_classlimit 1;<br /><br /> =================================================================<br /><br /> Thoughts?<br
/><br/> Best regards.<br /><br /> --<br /> Tatsuhito Kasahara<br /><a
href="mailto:kasahara.tatsuhito@oss.ntt.co.jp">kasahara.tatsuhito@oss.ntt.co.jp</a><br/><font color="#888888"><br />
--<br/> Sent via pgsql-hackers mailing list (<a
href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br/> To make changes to your
subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-hackers"
target="_blank">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/></font></blockquote></div><br /><br
clear="all"/><br />-- <br />gurjeet[.singh]@EnterpriseDB.com<br />singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo
}.com<br/><br />EnterpriseDB      <a href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br /><br />Mail
sentfrom my BlackLaptop device<br />