Re: Fwd: Re: Timeouts on connections

Andy Corteen <lbc(at)telecam(dot)demon(dot)co(dot)uk> writes:
> Can anyone shed some light on this one from the PostgreSQL end of
> things?

>> After maybe an hour or so of inactivity, the back-end connection to
>> Postgres (7.0.3 on RH Linux 6.2) closes, and the application is then
>> useless until restarted - I was unaware of this possibility, therefore
>> have not coded anything to deal with it.

Hm. There is certainly not any inactivity timeout in the backend
(though various people have unsuccessfully pestered us to add one ;-)).
If you're certain that the frontend app doesn't have one either, then
that leaves the transport mechanism. Are you using TCP or Unix-domain
connections?

If it's TCP, then a likely bet is that the problem is triggered by our
use of the TCP KEEPALIVE option to detect dead clients. If the client
machine fails to respond to a keepalive probe then the connection would
close after sufficient inactivity. However RFC1122 says that the
minimum idle time before a keepalive probe is two hours, so if you are
seeing a failure due to keepalive after only one hour, then both your
server and client network stacks are non-conformant :-(. So I'm not
totally sure about this guess.

What platform is the client running on, anyway?

Anyway it might be worth diking out the lines

if (setsockopt(port->sock, SOL_SOCKET, SO_KEEPALIVE,
&on, sizeof(on)) < 0)
{
perror("postmaster: StreamConnection: setsockopt(SO_KEEPALIVE)");
return STATUS_ERROR;
}

in src/backend/libpq/pqcomm.c to see if that changes the behavior or
not.

regards, tom lane