Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 687971EC85DC
	for <pgsql-admin@postgresql.org>; Fri, 10 Aug 2012 01:21:10 -0300 (ADT)
Received: from sss.pgh.pa.us ([66.207.139.130])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1Szgie-0001Ml-1i
	for pgsql-admin@postgresql.org; Fri, 10 Aug 2012 04:21:09 +0000
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q7A4KnB9013012;
	Fri, 10 Aug 2012 00:20:51 -0400 (EDT)
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Craig Ringer <ringerc@ringerc.id.au>
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
In-reply-to: <502470E8.4010807@ringerc.id.au>
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>
Comments: In-reply-to Craig Ringer <ringerc@ringerc.id.au>
	message dated "Fri, 10 Aug 2012 10:24:40 +0800"
Date: Fri, 10 Aug 2012 00:20:48 -0400
Message-ID: <13011.1344572448@sss.pgh.pa.us>
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201208/73
X-Sequence-Number: 39894

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.

As of 9.2 there's a "state_change" timestamp column in pg_stat_activity
that would provide a safer check, but it's not in existing releases ...

			regards, tom lane