On Fri, 2005-11-18 at 18:51, Otto Blomqvist wrote: > Hi, > > We are using PostgresDAC 2.2.1 and PostgreSQL 8.0.2 on > i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 4.0.0 > 20050412 (Red Hat 4.0.0-0.42). > > I perform a simple test as follows. > > 1. I connect to the database, which is located on a LAN. > > 2. I simulate Internet problems by unplugging the Ethernet cable of the > client. There is no PSQL activity going on. > > 3. Plug the ethernet cable back in > > 4. Run some sql, which gives me a Postgres SQL error -1, Server closed > connection unexpectedly > > So far so good. Problem is that the postmaster does not detect this > connection as dead and keeps it idle for an unknown amount of time. This is > a real problem for us because we use persistent connections to authorize > access to a custom built 68030 based system, which has a limited number of > "slots" that we can use. By not releasing a dead connection we are also > holding that 68030 slot busy. The real issue here is that TCP keepalive keeps the connection alive for a long time. The default on linux boxen is 2+hours. In our production environment, we dropped tcp_keepalive to 5 minutes. There are four settings in the linux kernel: net.ipv4.tcp_keepalive_intvl = 75 net.ipv4.tcp_keepalive_probes = 9 net.ipv4.tcp_keepalive_time = 500 the keepalive_time tells the kernel how long to wait to "ping" a connection after it's gone quiet. The probes and intvl tell it how many times to try and re-awaken it and how long to wait between each. So, with the settings shown above, a dead connection will wait 8.3 minutes, then execute a "ping" (not really a ping, it's on a lower level than a real ping would be) and then will wait 1.25 minutes and do it 9 times. So, in this scenario, an idle connection left from a network failure will take just under 20 minutes to clear.
