Re: Clients disconnect but query still runs

Robert James wrote:
> I see - thanks, Tom, for the informative explanation.
> In my experience admining high volume servers, I found this to a major
> failure pattern: Client tries query which seems to go on forever (either
> do to contention or resource exhaustion or some other problem), client
> gives up / fails / gets shut down or rebooted

The client should always make its best effort to notify the server if
it's disconnecting. How it's done depends on client OS, client program
language, etc, but it generally ends up meaning AT LEAST that the client
sends a TCP RST to the server to close the client <-> server socket.

I don't know off the top of my head if the server backend will
immediately notice an RST on the socket and terminate. If it doesn't,
then that's certainly something that'd be desirable.

If the client doesn't send an RST and just "vanishes" then of course the
server has no way to know anything's changed. As you say, you'd need to
have tcp keepalives in use to find out.

, yet the database is left
> hanging working on the sloooow query, which is probably consuming all of
> its resources. Perhaps the client restarts and tries again, now making
> the problem much worse, and the vicious cycle continues until the server
> is rebooted.

The server should never need to be rebooted. What about
pg_cancel_backend() ? What about killing the backends with SIGTERM (not
SIGKILL, -9) or similar?

--
Craig Ringer