X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 56BB19FB3F1
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 12 Jul 2006 14:39:45 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 72603-09 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 12 Jul 2006 14:39:39 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from cronos.madness.at (madness.at [217.196.146.217])
	by postgresql.org (Postfix) with ESMTP id AD2EC9FB419
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Jul 2006 14:39:39 -0300 (ADT)
Received: from mastermind.kaltenbrunner.cc ([83.215.233.60])
	by cronos.madness.at with esmtpsa (TLSv1:AES256-SHA:256)
	(Exim 4.62 (FreeBSD)) (envelope-from <stefan@kaltenbrunner.cc>)
	id 1G0igM-000EOB-Sm; Wed, 12 Jul 2006 19:39:35 +0200
Message-ID: <44B533D2.2010203@kaltenbrunner.cc>
Date: Wed, 12 Jul 2006 19:39:30 +0200
From: Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
User-Agent: Thunderbird 1.5.0.2 (X11/20060516)
MIME-Version: 1.0
To: "Craig A. James" <cjames@modgraph-usa.com>
CC: Magnus Hagander <mha@sollentuna.net>,
 pgsql-performance@postgresql.org
Subject: Re: Kill a session
References: <6BCB9D8A16AC4241919521715F4D8BCEA0FAE8@algol.sollentuna.se>
	<44B51896.1000808@modgraph-usa.com>
In-Reply-To: <44B51896.1000808@modgraph-usa.com>
X-Enigmail-Version: 0.94.0.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200607/115
X-Sequence-Number: 19975

Craig A. James wrote:
> Magnus Hagander wrote:
>>> This raises the question: Why doesn't Postgres have a "kill session"
>>> command that works?  Oracle has it, and it's invaluable; there is no
>>> substitute.  Various writers to these PG lists have raised the
>>> question repeatedly.  Is it just a matter that nobody has had the
>>> time to do it (which I respect!), or is there a reason why the
>>> Postgres team decided a "kill session" is a bad idea?
>>
>> I beleive the function to kill a backend is actually in the codebase,
>> it's just commented out because it's considered dangerous. There are
>> some possible issues (see -hackers archives) about sending SIGTERM
>> without actually shutting down the whole cluster.
>>
>> Doing the client-side function to call is the easy part.
>>
>> In many cases you just need to cancel a query, in which case you can use
>> pg_cancel_backend() for exmaple. If you need to actually kill it, your
>> only supported way is to restart postgresql. 
> 
> In other words, are you confirming that there is no way to kill a query
> from another process, other than shutting down the database?  My
> understanding of the documentation tells me I can't use cancel, because
> the process doing the killing isn't the original process.
> 
>>> But in spite earlier posting in these forums that say the killing the
>>> backend was the way to go, this doesn't really work.  First, even
>>> though the "postgres" backend job is properly killed, a "postmaster"
>>> job keeps running at 99% CPU, which is pretty useless.  Killing the
>>> client's backend didn't kill the process actually doing the work!
>>
>> Then you killed the wrong backend...
>> No queries run in postmaster. They all run in postgres backends. The
>> postmaster does very little actual work, other than keeping track of
>> everybody else.
> 
> It turns out I was confused by this: ps(1) reports a process called
> "postgres", but top(1) reports a process called "postmaster", but they
> both have the same pid.  I guess postmaster replaces its own name in the
> process table when it's executing a query, and it's not really the
> postmaster even though top(1) calls it postmaster.
> 
> So "kill -15 <pid>" is NOT killing the process -- to kill the process, I
> have to use signal 9.  But if I do that, ALL queries in progress are
> aborted.  I might as well shut down and restart the database, which is
> an unacceptable solution for a web site.
> 
> I'm back to my original question: How do you kill a runaway query
> without bringing down the whole database?  Is there really no answer to
> this?

are you maybe looking for pg_cancel_backend() ?

http://www.postgresql.org/docs/current/interactive/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL-TABLE

Stefan