I'm looking for a reliable way for a client to disconnect from a backend
such that any running query is terminated.
Right now, PostgreSQL doesn't seem to make an effort to detect a client
cancellation. For instance, if you do a "select pg_sleep(1000)" and then
kill -9 the client, the SELECT will remain running. That's not so much
of a problem for sleep, but if it's doing real work, then it's wasting a
lot of effort (and perhaps not terminating in any reasonable amount of
time).
And even if the client makes an effort to cancel and there are no major
network problems, then I still don't see a good method. Because the
cancellation request is sent out-of-band to the postmaster, then it's in
a race with the (asynchronous) query that you just sent. If the signal
reaches the backend before the query does, then the SIGINT becomes a
no-op (because it's still idle), and then the query arrives, and then
the client does PQfinish, the backend will still be alive doing a bunch
of needless work.
I have attached a simple C program that demonstrates the problem (must
be run from same host as PG because it uses SIGSTOP/SIGCONT to reproduce
race). After you run it, see how the "SELECT pg_sleep(1000)" is still
running, despite the client being disconnected.
There are two solutions that I see, neither of which look great:
1. Make a separate connection, and issue pg_terminate_backend() before
PQfinish. It works because a SIGTERM will not be a no-op on an idle
backend. This solution requires superuser privileges (not acceptable),
plus it's a little ugly.
2. Keep sending cancellation requests in a loop with a delay, consuming
input each time until PQisBusy() returns false. Obviously fairly ugly
and error prone, but is somewhat acceptable.
Any other ideas? There is no PQterminate, unforunately.
statement_timeout is not feasible, as the statement might legitimately
run for a very long time.
This is all compounded by the fact that terminating the backend directly
is no guarantee of proper shutdown, either:
http://archives.postgresql.org/pgsql-general/2009-03/msg00434.php
That means that there is no way to nicely and reliably shut down
postgresql from the client alone, nor from the server alone. The only
way is to send a SIGTERM to the backend *and* terminate the client
connection. Unless someone has a better idea?
Thoughts?
Regards,
Jeff Davis
/*
* Only works if run on the same host as postgres; can't work over a
* network because we need to be able to signal backend directly.
*/
#include <stdlib.h>
#include <stdio.h>
#include <libpq-fe.h>
#include <sys/types.h>
#include <signal.h>
#define ERRBUF_SIZE 256
int main(int argc, char *argv[])
{
char *conninfo;
PGconn *conn;
PGcancel *cancel;
char errbuf[ERRBUF_SIZE];
pid_t be_pid;
if (argc < 2)
{
fprintf(stderr, "must supply connection string as argument");
exit(1);
}
conninfo = argv[1];
conn = PQconnectdb(conninfo);
/* Check to see that the backend connection was successfully made */
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s",
PQerrorMessage(conn));
PQfinish(conn);
exit(1);
}
cancel = PQgetCancel(conn);
be_pid = PQbackendPID(conn);
printf("sending SIGSTOP to %d\n", be_pid);
kill(be_pid, SIGSTOP);
printf("sending query\n");
PQsendQuery(conn, "select pg_sleep(1000)");
printf("sending cancel\n");
PQcancel(cancel, errbuf, ERRBUF_SIZE);
printf("sending SIGCONT to %d\n", be_pid);
kill(be_pid, SIGCONT);
printf("disconnecting\n");
PQfreeCancel(cancel);
PQfinish(conn);
return 0;
}
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
