Re: Kill a session

> There have been dozens, perhaps hundreds, of entries in the
> pg-admin, pg-general, and pg-performance lists regarding
> killing a session, but as far as I can tell, there is no
> Postgres solution. Did I miss something?
>
> 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?

[snip]

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.

> 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...

> Second, the "KILLING SESSION" message to stderr is only
> printed in the PG log file sporadically. This confuses me,
> since the "KILLING SESSION" is printed by a *different*
> process than the one being killed, so it shouldn't be
> affected. So what happens to fprintf()'s output? Most of
> the time, I just get "unexpected EOF on client connection" in
> the log which is presumably the postmaster complaining that
> the postgres child process died.

No, that's the postgres chlid process complaining that your client
(CGI?) died without sending a close message.

> I know the kill_session() is working because it returns
> "true", and the job is in fact killed. But the query keeps
> running in postmaster (or is it something else, like a
> rollback?), and the stderr output disappears.

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.

//Magnus