Re: 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(at)fcot(dot)com>
To: zeos(at)perio(dot)unlp(dot)edu(dot)ar <zeos(at)perio(dot)unlp(dot)edu(dot)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(at)telecam(dot)demon(dot)co(dot)uk>
To: "Zeos mailing list" <zeos(at)perio(dot)unlp(dot)edu(dot)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(at)telecam(dot)demon(dot)co(dot)uk
>
>
> To unsubscribe mail to
> zeos-request(at)perio(dot)unlp(dot)edu(dot)ar with 'unsubscribe' as the subject
> home page: http://www.zeos.dn.ua
>
>

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

--
Best regards,
Andy mailto:lbc(at)telecam(dot)demon(dot)co(dot)uk

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