How to use tcp_keepalives settings in Postgresql?

Question

Postgresql has 3 keepalive settings for managing dropped connections (in postgresql.conf):

tcp_keepalives_count
tcp_keepalives_idle
tcp_keepalives_interval

By default these are 0.

The behavior I would like is for Postgresql to drop client connections after a period of time, should the client lose its network connection or go to sleep.

I am currently using these values:

tcp_keepalives_count = 1
tcp_keepalives_idle = 60
tcp_keepalives_interval = 60

I am running PostgreSQL 8.4 on Mac OS X, but it doesn't seem to have any effect. My test is that I lock a row in a table (using SELECT FOR UPDATE) and disconnect the workstation from the network. But in Postgresql I still see that workstation holding the lock.

I would expect that after the time has passed (60 seconds in this case) the connection would be terminated and the lock would be released.

Either I am doing something wrong or I am completely misunderstanding how this is supposed to work.

Any advice?

Answer 1

I think you need to configure your operating system instead. Changing keepalive parameters by programs is not widely supported yet. This should help you:
Using TCP keepalive to Detect Network Errors

Also your parameters are chosen badly. If tcp_keepalives_count=1 worked then even one lost keepalive packet will drop your connection. And single packets get lost often. I'd use the following in /etc/sysctl.conf on MacOSX/FreeBSD:
net.inet.tcp.keepidle = 60000
net.inet.tcp.keepintvl = 10000
OS will then drop connections at most 140 seconds (60 seconds of idle + 8 keepalive packets in 10 seconds intervals) after loosing connectivity.