Thread: Fwd: Re: Timeouts on connections

Postgres gurus,

Can anyone shed some light on this one from the PostgreSQL end of
things?

This is a forwarded message
From: Graham Fountain <graham@fcot.com>
To: zeos@perio.unlp.edu.ar <zeos@perio.unlp.edu.ar>
Date: Saturday, January 27, 2001, 10:09:30 PM
Subject: [zeos] Timeouts on connections

===8<==============Original message text===============
I have an application that does this too - only at the moment my app isn't
using Zeos, it uses the ODBC library through the BDE, so I am assuming then
that it is something to do with the back-end server. Up until seeing your
message I thought maybe it was something to do with the ODBC.  I'll now be
looking at the compile options of postgres to see if there is a setting in
there.

Perhaps another solution would be to have a function that is triggered by a
TTimer, perhaps every half hour that just does a simple query, that way it
isn't having excessively long inactive times.

BTW... I haven't converted to Zeos from ODBC yet - I'm a little afraid of
the nightmare that it would involve, but can anyone advise whether it would
provide a serious speed benefit, and is it more robust?
Regards,
Graham

----- Original Message -----
From: "Andy Corteen" <lbc@telecam.demon.co.uk>
To: "Zeos mailing list" <zeos@perio.unlp.edu.ar>
Sent: Saturday, January 27, 2001 3:57 AM
Subject: [zeos] Timeouts on connections


> I have an application that can remain open on a user's desktop all
> day, which the users like (startup times etc...). This is a pilot of a
> conversion to SQL from a tables-based solution that has worked well
> for several years.
>
> 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.
>
> Is there an event that declares that the back-end has disconnected?
> and/or is there some simple test that will allow me to probe that the
> connection is still live before asking for the data, so that the users
> are unaware of these "background" issues that do not concern them?
>
> --
> Best regards,
>  Andy                          mailto:lbc@telecam.demon.co.uk
>
>
> To unsubscribe mail to
> zeos-request@perio.unlp.edu.ar with 'unsubscribe' as the subject
> home page: http://www.zeos.dn.ua
>
>

===8<===========End of original message text===========


--
Best regards,
 Andy                            mailto:lbc@telecam.demon.co.uk

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