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, 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.
Is there no way to have the OS interrupt the postgres process when a TCP/IP disconnect happens? Or is the OS also in the dark that the TCP/IP connection was dropped? I believe that there is a way to monitor this using TCP/IP keep alives.
Or perhaps Postgres could check once every minute? Either way, in my experience, solving this would be a major boon to high volume servers, at least in the usage patterns I've worked with.
On Mon, Jul 27, 2009 at 9:49 PM, Tom Lane
<tgl@sss.pgh.pa.us> wrote:
Robert James <
srobertjames@gmail.com> writes:
> Hi. I noticed that when clients (both psql and pgAdmin) disconnect or
> cancel, queries are often still running on the server. A few questions:
> 1) Is there a way to reconnect and get the results?
> 2) Is there a way to tell postgres to automatically stop all queries when
> the client who queried them disconnects?
No.
> 3) Is there a way to see all queries whose clients have disconnected?
No.
> 4) And finally: Why is this the behavior?
It's not easy to tell whether a client has disconnected (particularly if
the network stack is unhelpful, which is depressingly often true).
Postgres will cancel a query if it gets told that the connection's been
dropped, but it will only discover this when an attempt to output to the
client fails. It does not spend cycles looking aside to see if the
connection has dropped when it is doing something that doesn't involve
output to the client.
If your client code is polite enough to send a cancel request before
disconnecting, that should terminate the query reasonably promptly.
But just "yanking the plug" doesn't do that.
regards, tom lane