Greg Stark wrote:
Right, you'll only get SIGURG if there's actually any urgent data received. The client would have to actively send such data periodically. That would make this a portability headache since it wouldn't just be an add-on which would fail gracefully if it's unsupported.
It'd also have the same issue as relying on keepalives - ie transient network drop-outs would be much more likely to cause an unnecessary query cancel. Worse, the client wouldn't even know about it, because it was unreachable at the time the server sent it a RST, so it'd be waiting for an answer from the server that'd never come...
What does work well is occasionally poking the socket with recv(..., MSG_DONTWAIT) while doing other work.
The problem with this is that it introduces spurious failures for transient network failures.
Yep, and often failures where only one side notices (unless _both_ sides are relying on keepalives and are checking the connection status). Ick.
Also it requires the server to periodically take time out from processing the query to do this.
This aspect I'm not to bothered about. I doubt it'd cost anything detectable if done a few times a minute - unless it required restructuring of query processing to accomodate it. In that case, no way.
I think we want a zero-cost method which will interrupt processing if the client actively disconnects. If there's a network failure we'll find out about it in the normal course of events.
Personally, I'm with you. I think the _real_ problem here is clients that're just giving up and vanishing without issuing a query cancel or, apparently, even closing the connection.
It's _not_ hard to time out on a query and use another connection to cancel the backend. If you've set a server-side timeout, of course, there's no need to do anything, but if you're timing out client-side or your user has cancelled the request, it's still not exactly hard to clean up after yourself. It was perhaps twenty minute's work to implement a generic query cancel command with Java/JDBC - just spawn another thread or grab one from a worker pool, open a new Pg connection, issue the backend cancel, and close the connection. In the thread that just had its backend cancelled you receive an informative SQLException.
In fact, I'm not even sure _how_ one goes about exiting without sending an RST. A quick check shows that when I `kill -9' a process with an open client socket (ssh, in this case) the OS sends a FIN, and responds to the server's FIN,ACK with its own ACK. So the OS is closing the socket for the dead process. If I try this with a `psql' process, the server cleans up the orphaned backend promptly.
So, barring network breaks (wifi down / out of range, ethernet cable fell out, etc etc) how is the OP managing to leave backends running queries? Hard-resetting the machine?
-- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
