Re: Clients disconnect but query still runs - Mailing list pgsql-general

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