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

On Thu, Jun 04, 2009 at 10:22:41PM -0400, Robert Haas wrote:
> The only thing I don't like about this is that I think it's kind of a
> hack to shove the <IDLE> in transaction designation and the query
> string into the same database column. I've never liked having to
> write:
>
> select sum(1) from pg_stat_activity where current_query = '<IDLE> in
> transaction';
>
> ...and I like it even less if I now have to modify that query to use
> "like". We should find some way to represent this as structured
> data... maybe make a separate column called "idle" that's a boolean,
> or something, and let the query column contain the most recent query
> (whether or not it's still executing).

I like this idea a lot. Possibly it would be useful to have the end time
of the last query too, then one could find idle sessions that were old and
truly idle rather than just waiting for a busy client to send the next query.

select ... from pg_stat_activity
where idle
and last_statement_endtime < now() - interval '1 minute';

-dg

--
David Gould daveg(at)sonic(dot)net 510 536 1443 510 282 0869
If simplicity worked, the world would be overrun with insects.