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
