On 7/09/2011 10:00 AM, Jeff Davis wrote:
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.
pg_sleep isn't a good test. In fact, Pg _does_ make an effort to detect when a client dies, and will try to terminate the query. It does this via explicit checks at various points, none of which are reached while Pg is idling in a sleep() syscall. During more typical query processing you'll usually find that a query gets terminated when the client dies.
Pg must find out when the client dies, though. If the client just goes away - such as with a laptop on wifi that wanders out of range - it won't know about it until it next attempts to send data to the client. To address this, if you want reliable client dropout detection, you need to enable tcp keepalives and set them to quite aggressive so the OS will periodically test the connection for aliveness.
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.
Yeah, this bit frustrates me too. Not only is the request async, but it requires the establishment of a new full-featured database connection first. That's a mess with pooling and sharding/clustering setups where there's no guarantee the connection will go to the same host. It's also expensive in terms of round trips, setup work on the server, and sheer time taken. I found supporting query cancels to be a right PITA and was never satisifed with the solution I ended up with in my (Java/PgJDBC) app.
I'd love to see Pg accept OOB cancel requests done via lightweight connections that don't go through the whole setup process. If the server sent a statement "cookie" when executing a statement, the client could hang onto that and use it to issue a cancel for that statement and only that statement by establishing a new connection to the server and sending that cookie rather than the usual negotiation and auth process. There'd be no need to go through full auth or even bother with SSL, because it's a one-time random (or hash-based) code. Pooling systems could send this to _all_ servers, or it could be prefixed with a server identifier that helped poolers route it to the right server.
The same statement cookie could be used to support full connection-based cancellation by passing it to a "pg_cancel_statement('blahcookieblah');" function.
There's probably no safe way (short of a fully threaded backend - and I did say "safe") to avoid the need for a new control connection and use a single tcp connection for everything, though. Sending the cancel message in-band via the regular connection will work for non-SSL connections, since the server can periodically check for new input and process it without risking blocking. This cannot work with SSL though, as there's no way to tell if data waiting on the socket is a whole SSL message or only a partial one that'll block waiting for new input when read. Even were that problem worked around with a separate socket reader thread that _can_ block, the backend still has to do periodic checks for input, so it'd suffer from some of the same issues as the current approach.
Any other ideas? There is no PQterminate, unforunately. statement_timeout is not feasible, as the statement might legitimately run for a very long time.
A real fix requires backend enhancements like unique statement identifiers, IMO.
-- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
