Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 09E23192E3B7
	for <pgsql-admin@postgresql.org>; Fri, 10 Aug 2012 02:08:52 -0300 (ADT)
Received: from mail-pb0-f46.google.com ([209.85.160.46])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ringerc@ringerc.id.au>) id 1SzhSp-00022U-GJ
	for pgsql-admin@postgresql.org; Fri, 10 Aug 2012 05:08:52 +0000
Received: by pbbrr13 with SMTP id rr13so2102257pbb.19
	for <pgsql-admin@postgresql.org>; Thu, 09 Aug 2012 22:08:30 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding
	:x-gm-message-state;
	bh=q/izh5Y8qWIgME6cWdgp91m8zGHBM/p80jQxaavAJE0=;
	b=kq3tdPifysykNoN2IDGx0NUPaXwk+p9t3Lc+FWwFqzfUBbF449DRrnG9bIcQI3Qn6L
	KHa5w7JtijroVHJeRsdOj7ehxd8diOLc9B8g7kQwp28KPP4l4LAXwcv3RVCYfnUmGnK0
	Ea14qi/wPQRip3HQgMtcuPXQIXHjSeTwJfgCMSo93A2O/SduipEKAneH6UG8XKrtasO1
	EvvUtfKH0E02KsnZ0L0Khlq0JyT0pFg5FIsWl0Nhz7Lsf6rMPu8TN3layQkOOdvNe4Tl
	An0H3+au50/A1RYWCemMPzfHUNBcKCOxF0yzfncYaCaeOH6FXoynme6DDLDMfDZVKx+h
	RVZg==
Received: by 10.68.212.161 with SMTP id nl1mr9620834pbc.84.1344575310676;
	Thu, 09 Aug 2012 22:08:30 -0700 (PDT)
Received: from ayaki.localdomain (124-169-173-54.dyn.iinet.net.au.
	[124.169.173.54])
	by mx.google.com with ESMTPS id sk5sm2580364pbc.7.2012.08.09.22.08.27
	(version=SSLv3 cipher=OTHER); Thu, 09 Aug 2012 22:08:29 -0700 (PDT)
Message-ID: <50249748.8080106@ringerc.id.au>
Date: Fri, 10 Aug 2012 13:08:24 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120717 Thunderbird/14.0
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Greg Williamson <gwilliamson39@yahoo.com>,
	"pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Question about ALTER TABLE DROP CONSTRAINT on 9.1 -- psql crashes
References: <1344557082.43876.YahooMailNeo@web125906.mail.ne1.yahoo.com>
	<8579.1344557984@sss.pgh.pa.us>
	<1344558910.70207.YahooMailNeo@web125906.mail.ne1.yahoo.com>
	<1344562260.90632.YahooMailNeo@web125901.mail.ne1.yahoo.com>
	<10482.1344564417@sss.pgh.pa.us> <502470E8.4010807@ringerc.id.au>
	<13011.1344572448@sss.pgh.pa.us>
In-Reply-To: <13011.1344572448@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Gm-Message-State: 
 ALoCoQkEfm9gu4i2hPXKNnXny0iRy6mW/81qSAQudM1S7nDjuJAUr07YVN46Y16UP+Gjo5XirseM
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201208/74
X-Sequence-Number: 39895

On 08/10/2012 12:20 PM, Tom Lane wrote:
> Craig Ringer <ringerc@ringerc.id.au> writes:
>> On 08/10/2012 10:06 AM, Tom Lane wrote:
>>> That sure sounds like the source of your problem.  It might be sane if
>>> it killed only processes that *had been idle* for at least three
>>> seconds, but I'm not sure there is any easy way to determine that ...
>
>> wouldn't:
>
>> select * from pg_stat_activity
>> where current_query = '<IDLE> in transaction'
>> AND query_start < current_timestamp - INTERVAL '3 seconds';
>
>> do it?
>
> No, that would find sessions that were idle and whose last command
> started at least 3 seconds ago.  But it might have completed only
> microseconds ago.  The symptoms Greg is describing are consistent
> with this kill script running during a short interval between his
> index-build command and his COMMIT.

Eh. For some reason I thought query_start was updated whenever 
current_query was set, so it'd be updated when the session went idle. 
Nonesensical in retrospect.

Does this approach look sane? Note idlers and then reap them if they 
haven't started new queries next pass:

CREATE UNLOGGED TABLE reap (
   procpid integer,
   query_start timestamptz,
   added timestamptz
);

-- Run every 20s or whatever to note idlers and when we noticed them
insert into reap (procpid,query_start,added)
select procpid, query_start, current_timestamp
from pg_stat_activity where current_query = '<IDLE> in transaction';

-- Clean out old entries and kill idlers
WITH r AS (
   DELETE FROM reap r2
   WHERE r2.added + INTERVAL '10 seconds' < current_timestamp
   RETURNING *
)
SELECT r.procpid, pg_terminate_backend(r.procpid)
FROM r INNER JOIN pg_stat_activity s
   ON (r.procpid = s.procpid AND r.query_start = s.query_start )
GROUP BY r.procpid;

ie "where we've noted more than 1 minute ago that a pid was idle, and 
query_start hasn't changed so no new query has been run, kill the backend".

Of course, there's race where the backend could be starting a new query 
even as you kill it for idleness, but that's true in any 
idle-in-transaction control scheme (other than having a true transaction 
timeout feature *within* the backend).

--
Craig Ringer