From: Craig A. James <cjames@modgraph-usa.com>
To: Magnus Hagander <mha@sollentuna.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Kill a session
Date: Wed, 12 Jul 2006 08:43:18 -0700
Message-ID: <44B51896.1000808@modgraph-usa.com> (raw)
In-Reply-To: <6BCB9D8A16AC4241919521715F4D8BCEA0FAE8@algol.sollentuna.se>
References: <6BCB9D8A16AC4241919521715F4D8BCEA0FAE8@algol.sollentuna.se>
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?
Thanks,
Craig
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cjames@modgraph-usa.com, mha@sollentuna.net
Subject: Re: Kill a session
In-Reply-To: <44B51896.1000808@modgraph-usa.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox