Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id CF2DE632E36
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Fri, 24 Jul 2009 02:41:33 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 08007-04
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Fri, 24 Jul 2009 05:41:23 +0000 (UTC)
X-Greylist: delayed 00:12:59.899282 by SQLgrey-1.7.6
Received: from a.mail.sonic.net (a.mail.sonic.net [64.142.16.245])
	by mail.postgresql.org (Postfix) with ESMTP id AD9A56329F0
	for <pgsql-hackers@postgresql.org>;
	Fri, 24 Jul 2009 02:41:22 -0300 (ADT)
Received: from newshell.sonic.net (bolt.sonic.net [208.201.242.19])
	by a.mail.sonic.net (8.13.8.Beta0-Sonic/8.13.7) with ESMTP id
	n6O5SBu6021908
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO);
	Thu, 23 Jul 2009 22:28:11 -0700
Received: from bolt.sonic.net
	(IDENT:9HDI0qON2Q/7zhUbw6uqxdBifxBHZ7Rz@localhost.localdomain
	[127.0.0.1])
	by newshell.sonic.net (8.13.6/8.12.1) with ESMTP id n6O5SB9l024223;
	Thu, 23 Jul 2009 22:28:11 -0700
Received: (from daveg@localhost)
	by bolt.sonic.net (8.13.6/8.12.1/Submit) id n6O5SA8U024222;
	Thu, 23 Jul 2009 22:28:10 -0700
Date: Thu, 23 Jul 2009 22:28:10 -0700
From: daveg <daveg@sonic.net>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Tatsuhito Kasahara <kasahara.tatsuhito@oss.ntt.co.jp>,
	Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	Greg Stark <stark@enterprisedb.com>, Asko Oja <ascoja@gmail.com>,
	Simon Riggs <simon@2ndquadrant.com>, decibel <decibel@decibel.org>,
	pgsql-hackers@postgresql.org
Subject: Re: display previous query string of idle-in-transaction
Message-ID: <20090724052810.GF18614@sonic.net>
References: <49C9F49B.8020003@oss.ntt.co.jp>
	<1238091273.16568.530.camel@ebony.2ndQuadrant>
	<49CC75E3.7010101@oss.ntt.co.jp>
	<1238139418.16568.555.camel@ebony.2ndQuadrant>
	<A90F3088-EC12-4802-BFC0-BE7BFEBA08F9@decibel.org>
	<ecd779860905122156o48b3d07al75d2d21751c2a5bd@mail.gmail.com>
	<4136ffa0906030724g72a56165s33819f3060002dcb@mail.gmail.com>
	<4A264377.EE98.0025.1@wicourts.gov>
	<4A287AF3.9000301@oss.ntt.co.jp>
	<603c8f070906041922u7eaf0f76l8c2b7cbd3049c69b@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <603c8f070906041922u7eaf0f76l8c2b7cbd3049c69b@mail.gmail.com>
User-Agent: Mutt/1.4.2.3i
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=-10 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200907/1542
X-Sequence-Number: 142164

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@sonic.net      510 536 1443    510 282 0869
If simplicity worked, the world would be overrun with insects.