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

From Csaba Nagy
Subject Re: Clients disconnect but query still runs
Date
Msg-id 1248946045.24829.19.camel@pcd12478
Whole thread Raw
In response to Re: Clients disconnect but query still runs  (Greg Stark <gsstark@mit.edu>)
Responses Re: Clients disconnect but query still runs
List pgsql-general
Hi all,

On Thu, 2009-07-30 at 11:02 +0200, Greg Stark wrote:
> On Thu, Jul 30, 2009 at 7:43 AM, Craig
> Ringer<craig@postnewspapers.com.au> wrote:
> > On Wed, 2009-07-29 at 14:56 +0100, Greg Stark wrote:
> > What does work well is occasionally poking the socket with recv(...,
> > MSG_DONTWAIT) while doing other work. Program attached. TCP keepalives
> > seem to work very well at least on my Linux test system, and it's easy
> > to test for a dud connection using recv(...) with the MSG_DONTWAIT and
> > (if desired) MSG_PEEK flags. If the connection has exited cleanly it'll
> > return a zero-size read; if the connection has dropped due to keepalive
> > failure it'll return ETIMEDOUT.
>
>
> The problem with this is that it introduces spurious failures for
> transient network failures. Also it requires the server to
> periodically take time out from processing the query to do this. 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.

Sorry, I have to disagree here. If there's a spurious network error, you
have usually bigger problems. I prefer to have the connection killed
even if the network recovers than risk an idle in transaction connection
to live forever when the client/network crashes for any reason. In case
of network failure the connection will probably be cleaned eventually,
but it did happen to me that a client machine crashed in the middle of a
transaction while not executing any SQL, and that connection stayed
until I killed it manually. A simple ping to the client would have
cleared the fact that the client is not there anymore. I would also be
happy to pay the cost of pinging the clients let's say once per a minute
(or configurable interval). Considering that the connections are one to
one with a client, it's enough to have a single timer which periodically
signals each backend to ping it's client, but this is implementation
details for which I have no clue how it would be best, the main thing
is: I would love to have this functionality. It's extremely hard to
secure all clients against crash, and a crash of one of the clients in
the middle of a transaction can have very bad consequences (think
indefinitely stucked open transaction).

Cheers,
Csaba.



pgsql-general by date:

Previous
From: Greg Stark
Date:
Subject: Re: Clients disconnect but query still runs
Next
From: Moe
Date:
Subject: Re: How do I run PG Tuning Wizard on Linux?