Re: Fwd: Re: Timeouts on connections

Andy Corteen <lbc@telecam.demon.co.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