On Wed, 2011-09-07 at 14:46 +0800, Craig Ringer wrote: > > 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_sleep is not merely a wrapper around the sleep system call, it does call CHECK_FOR_INTERRUPTS() periodically. Also, you can see that pg_sleep can be easily canceled if the signal arrives while the query is actually running (try in psql, or try removing the SIGSTOP/SIGCONT signals from the C code I attached to the first message). Try with a large cartesian product and you should get the same problem. > 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. How does it know, even on a good network connection, when the client disconnects? I attached a reproducible case, so you should see what I'm talking about. > 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. I'd be happy if it just detected a disconnect that the OS already knows about, e.g. explicitly closing the socket. > I'd love to see Pg accept OOB cancel requests done via lightweight > connections that don't go through the whole setup process. It does that for cancel (see PQcancel), but there is no equivalent for termination. > 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. That's not too far from what's already done -- again, see the source for PQcancel() and processCancelRequest(). Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
