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

On Fri, Jul 24, 2009 at 10:47 AM, Kevin
Grittner<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> daveg <daveg(at)sonic(dot)net> wrote:
>> On Thu, Jun 04, 2009 at 10:22:41PM -0400, Robert Haas wrote:
>
>>> 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).
>
> +1
>
>> 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';
>
> +1

Hmm, I don't think we'd need two columns for this, actually. You
could just have one column last_statement_endtime (not sure if it's
the best name, but something along those lines) which would be NULL if
the statement was still in progress and the appropriate timestamp if
not. You could infer idle from whether or not that column was NULL.

> Of course, you might be more interested in those which are idle in a
> transaction, but that's easily done with these changes -- just throw
> in xact_start IS NULL.

Surely if xact_start is NULL it is not in a transaction at all?

...Robert