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
