Skip site navigation (1) Skip section navigation (2)

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

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: daveg <daveg(at)sonic(dot)net>, Simon Riggs <simon(at)2ndquadrant(dot)com>, decibel <decibel(at)decibel(dot)org>, Greg Stark <stark(at)enterprisedb(dot)com>, Asko Oja <ascoja(at)gmail(dot)com>, Tatsuhito Kasahara <kasahara(dot)tatsuhito(at)oss(dot)ntt(dot)co(dot)jp>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: display previous query string of idle-in-transaction
Date: 2009-07-24 14:58:15
Message-ID: 603c8f070907240758g34c1c54fp9a618e86adcc49a0@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Kevin GrittnerDate: 2009-07-24 15:06:32
Subject: Re: display previous query string of idle-in-transaction
Previous:From: Kevin GrittnerDate: 2009-07-24 14:47:06
Subject: Re: display previous query string of idle-in-transaction

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group