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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
