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?

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