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.ukTo 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