Re: Disconnecting and cancelling a statement - Mailing list pgsql-general

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