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

Tatsuhito Kasahara <kasahara(dot)tatsuhito(at)oss(dot)ntt(dot)co(dot)jp> wrote:

> So, main purpose of displaying the last query string is ..
> - check whether "idle in transaction (running long time) process
> after SOME SQL" is exists or not.
> - check the content of "SOME SQL".

The feature could be achieved by an extension module using new executor
hooks in 8.4. It is just like contrib/pg_stat_statements; Backends store
their queries history in process-local or shared memory. If you store
queries in local memory, you could dump them at some of callback routines
called at the end of transaction. On the other hand, if you use shared
memory, it might be possitble to define a SRF function which return history
of queries:

CREATE FUNCTION query_history(backend_pid integer) RETURNS SETOF text;

Required memory for query history is not so much.
"1kB of query text * 100 connection * 10 queries" consumes just 1MB.

You can discard old queries at the end of transaction or out of memory.
If there are some interesting queries in it, you can dump them into
server logs. For example, logging configurations something like
'idle_in_transaction_min_duration' and 'total_transaction_min_duration'
will be possible.

I'm not sure this feature should be in the core or not.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center