Re: Clearing out old idle connections

[Scott Marlowe <smarlowe(at)g2switchworks(dot)com>]

On Wed, 2006-05-24 at 14:00, Tom Lane wrote:
> "Florian G. Pflug" <fgp(at)phlo(dot)org> writes:
> > You can (at least on linux, I assume it's the same for BSD) set the 
> > "keepalive" flag of a connection. This results in empty packets being
> > sent every 30 seconds or so, and the connection is reported to be dead
> > if no ACK is received within a timeout.
> 
> > I don't know if postgres has a flag to disable/enable this. If not, I'd
> > guess it would be quite trivial to add that.
> 
> Postgres has always (well, for as long as I can remember) enabled
> keepalive checks on client TCP connections.  However, the default
> timeouts on TCP keepalive are not "30 seconds or so", but well over
> an hour according to the RFC specifications.
> 
> Some platforms allow you to fool with the timeouts, some don't.
> If you have one that does, recent PG versions will let you configure
> that.
> 
> I'm not sure whether this will actually solve the OP's problem, because
> if the clients are actually still there but not doing anything, the
> connection is not going to time out.  This is mainly something that
> helps with flaky networks, broken Windoze boxes that forget they have
> connections, that sort of thing...

Note that we had a problem like this because the router / firewalls
between our app servers and postgresql servers had a timeout of 30
minutes, and would drop the idle connections after that time.  Setting
our tcp_keepalive to 5 minutes or so made the problem of the postgresql
clients seeing broken connections go away.