Re: Kill a session

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux