Re: New to Postgresql - Backend timeout /JDBC

I was told that by a reasonably proficient programmer who has
UNIX kernel level experience as do I. I am not trying to toot
mine or anyone's horn, merely trying to set the stage for a
reasonably technical discussion. It seems you know exactly
what I am talking about.

I make the assertion that a servlet which has a connection open
to the database at all times (a servlet/tomcat connection object)
and is physically located on the same LAN as the database could
easily have a Internet user connected remotely step away from
his/her desk for an hour or more and then return to run a
JDBC statement. This would cause the JDBC driver to attempt
to use the connection object. In using this connection object
I am told, and will verify personally, that the information on the
closed connection may take 30 seconds to inform the JDBC
client running in the middle tier that the connection is closed
and that code needs to be run to reconnect this connection.
In my oracle implementation I keep one to four connection
objects open at all times for immediate service to the database
knowing that I have auto-commit on and that (at least empirically
under Oracle) the connection object will be valid and not broken
prematurely due to SO_KEEPALIVE being set. If the JDBC client
responded quickly to the invalid connection via the isClosed() method
on the connection (something I need more statistics on) then
I would not have posed this connection question. In addition,
Oracle allows prepared statements and statements in general
to be processed simultaneously and therefore I really only need
one connection if auto-commit is on.

I am sorry if I was long winded on this issue. I have a need
to have a very scaleable middle tier and am willing to code
around this issue with 1) a thread to run every 1/2 to 1 hour
on each connection object to keep it alive or 2) open a connection
every time (only scaleable by killing it with multiple middle
tier machines/hardware).

One final question: are you saying that I can reset the
SO_KEEPALIVE to a longer value to keep my connection
object vaild? If so could you point me in the proper source
direction to set this value. I will start by searching for the
KEEPALIVE code.

TIA as I very much appreciate any info on this subject. I
like postgresql very much and besides the sysdate issue
from Oracle to postgresql I was done with the port within,
including installation of the database and creation of my db objects,
a couple of hours. This impressed me very much. I am sure
I will find further things to address but am very pleased so far.

At 11:55 AM 1/31/2002 -0500, Tom Lane wrote:
>"Peter V. Cooper" <pvcooper(at)gte(dot)net> writes:
> > I am told that the backend to postgresql will automatically
> > timeout and close a JDBC client connection.
>
>There is most certainly no such timeout in the backend. Who told
>you that?
>
>We do run TCP connections with SO_KEEPALIVE set, which will cause
>the connection to be timed out if the far-end kernel stops responding
>for a sufficiently long time (IIRC, the RFC-mandated timeout is on
>the order of an hour). I doubt this would be likely to cause any
>problems, though. Certainly a slow application would not cause a
>failure at the TCP level.
>
> regards, tom lane