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?
The rest of this email is just to illustrate the convoluted solution I've had
to adopt, and even with this, I can't get it to work quite right.
Background: In our web app, we give our users a fair amount of power to formulate
difficult queries. These long-running queries are fork/exec'd from the Apache CGI, and
we give the user a "job status" page, with the option to kill the job.
I can kill off the CGI, since Apache owns the process. But the "stock answer"
of
kill -2 backend-pid
won't work, because I don't want my Apache jobs running as super-user (!) or as
setuid processes.
So here's my solution: Install a couple of C extensions like this:
Datum get_session_id(PG_FUNCTION_ARGS)
{
PG_RETURN_INT32(getpid());
}
Datum kill_session(PG_FUNCTION_ARGS)
{
int4 session_id, status;
session_id = PG_GETARG_INT32(0);
fprintf(stderr, "KILLING SESSION: %d, 15\n", session_id);
status = kill(session_id, 15);
PG_RETURN_BOOL((status == 0) ? true : false);
}
These are installed with the appropriate "CREATE OR REPLACE ..." sql. Although
this is dangerous (anyone who can log in to Postgres can kill any Postgres job!), its
safe enough in a controlled enviroment. It allows an Apache CGI to issue the kill(2)
command through the Postgres backend, which is running as the Postgres user, and thus has
permission to do the deed. When I start a job, I record the backend's PID, which allows
another process to connect and kill the first one. Alright, it's a hack, but it's the
best I could think of.
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!
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.
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.
Thanks,
Craig
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
