Re: Clearing out old idle connections

Richard Huxton wrote:
> Gavin Hamill wrote:
>> Hullo :)
>>
>>
>> We have pg 8.1.3 and for whatever reason (network blips, poor pooling
>> on behalf of the client, etc.) we sometimes see a large number
>> (dozens) of old connections in the idle state which never get reused.
>
> They should expire based on your TCP/IP settings. It's a TCP/IP
> connection timeout issue really, PG never gets to see them.
The problem is that tcp doesn't periodically send "keepalive" packets by
default, so if the client suddenly disappears (e.g if the machine it's
running is is disconnected from the network) postgres has no chance to
notice this until it tries to send data to the client, and receives a
timeout. But if a connection is idle, then postgres will never try to
send data to the client, and so it never notices that the connection is
in fact dead.

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.

greetings, Florian Pflug