Thread: Re: New to Postgresql - Backend timeout /JDBC
The JDBC client here is really the middle layer in a three tier configuration where the JDBC client tries to keep an open connection to the server at all times in order to more quickly respond to web page requests from the HTML / HTTP browser front end. I have verified that when the JVM is on the same machine as the server there is not a problem. The two other cases separate machines for the server and the JDBC client on a LAN and separate machines for the server and the JDBC client over a WAN. I am trying to verify or deny both cases. Anyone with further comments, experiences or solutions than Tom Lane? BTW, the information that the connection timed out when on separate networks seems to be around 5 minutes according to the place where the time out was being experienced. >To: "Peter V. Cooper" <pvcooper@gte.net> >cc: pgsql-bugs@postgresql.org >Subject: Re: [BUGS] New to Postgresql - Backend timeout /JDBC >Comments: In-reply-to "Peter V. Cooper" <pvcooper@gte.net> > message dated "Thu, 31 Jan 2002 09:40:58 -0800" >Date: Thu, 31 Jan 2002 12:57:56 -0500 >From: Tom Lane <tgl@sss.pgh.pa.us> > >"Peter V. Cooper" <pvcooper@gte.net> writes: > > 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. > >If true (which I doubt), the problem is *entirely* on the client side. >Neither the backend nor the TCP transport layer would have the >slightest difficulty with this scenario. The KEEPALIVE timeout I >mentioned would only come into play given an hour-long connectivity >failure of your LAN, or a system-level crash of your client machine, >neither of which are likely to result from a user taking a lunch break. > >However, I'm not very familiar with Java and so I cannot dismiss the >possibility that some layer inside the JVM might take it upon itself >to close an open TCP connection after a period of inactivity. > >If you are able to reproduce a problem of this sort then you need to be >looking inside the Java code. The backend is not causing it. > >The pgsql-jdbc list might be a better place to ask if anyone knows of >such problems in a JDBC context. Not sure how many JDBC people read >pgsql-bugs. > > regards, tom lane
Peter, There really is no difference as far as the jvm/jdbc driver are concerned. They don't know they are on a local machine, or a remote machine. I keep connections open across machines for days. Are you using ipchains, or some other firewall software that could be shutting down an idle connection? Dave -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Peter V. Cooper Sent: Wednesday, February 06, 2002 11:57 AM To: pgsql-jdbc@postgresql.org Cc: pvcooper@gte.net Subject: Re: [JDBC] New to Postgresql - Backend timeout /JDBC The JDBC client here is really the middle layer in a three tier configuration where the JDBC client tries to keep an open connection to the server at all times in order to more quickly respond to web page requests from the HTML / HTTP browser front end. I have verified that when the JVM is on the same machine as the server there is not a problem. The two other cases separate machines for the server and the JDBC client on a LAN and separate machines for the server and the JDBC client over a WAN. I am trying to verify or deny both cases. Anyone with further comments, experiences or solutions than Tom Lane? BTW, the information that the connection timed out when on separate networks seems to be around 5 minutes according to the place where the time out was being experienced. >To: "Peter V. Cooper" <pvcooper@gte.net> >cc: pgsql-bugs@postgresql.org >Subject: Re: [BUGS] New to Postgresql - Backend timeout /JDBC >Comments: In-reply-to "Peter V. Cooper" <pvcooper@gte.net> > message dated "Thu, 31 Jan 2002 09:40:58 -0800" >Date: Thu, 31 Jan 2002 12:57:56 -0500 >From: Tom Lane <tgl@sss.pgh.pa.us> > >"Peter V. Cooper" <pvcooper@gte.net> writes: > > 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. > >If true (which I doubt), the problem is *entirely* on the client side. >Neither the backend nor the TCP transport layer would have the >slightest difficulty with this scenario. The KEEPALIVE timeout I >mentioned would only come into play given an hour-long connectivity >failure of your LAN, or a system-level crash of your client machine, >neither of which are likely to result from a user taking a lunch break. > >However, I'm not very familiar with Java and so I cannot dismiss the >possibility that some layer inside the JVM might take it upon itself to >close an open TCP connection after a period of inactivity. > >If you are able to reproduce a problem of this sort then you need to be >looking inside the Java code. The backend is not causing it. > >The pgsql-jdbc list might be a better place to ask if anyone knows of >such problems in a JDBC context. Not sure how many JDBC people read >pgsql-bugs. > > regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
That is an interesting comment. I do not know the answer but I will find out. I had a similar problem with the Oracle connection when it was put on a VPN. I believe you may be correct that a firewall or VPN might be involved in the scenario. I am trying to simulate the problem before I put in an installation and have an unhappy customer. I thank you for your comments, since you keep open connections across disparate networks then I feel better about my upcoming installation. The install site has the PG server on a 192.168.1.X and the JDBC Client on a 192.168.2.X with a high speed router in between. Thanks again. At 12:15 PM 2/6/2002 -0500, Dave Cramer wrote: >Peter, > >There really is no difference as far as the jvm/jdbc driver are >concerned. They don't know they are on a local machine, or a remote >machine. I keep connections open across machines for days. > >Are you using ipchains, or some other firewall software that could be >shutting down an idle connection? > >Dave > >-----Original Message----- >From: pgsql-jdbc-owner@postgresql.org >[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Peter V. Cooper >Sent: Wednesday, February 06, 2002 11:57 AM >To: pgsql-jdbc@postgresql.org >Cc: pvcooper@gte.net >Subject: Re: [JDBC] New to Postgresql - Backend timeout /JDBC > > >The JDBC client here is really the middle layer in a three tier >configuration where the >JDBC client tries to keep an open connection to the server at all times >in >order to more >quickly respond to web page requests from the HTML / HTTP browser front >end. I have verified that when the JVM is on the same machine as the >server there is not a problem. The two other cases separate machines for >the server and >the JDBC >client on a LAN and separate machines for the server and the JDBC client > >over a WAN. >I am trying to verify or deny both cases. Anyone with further comments, >experiences or >solutions than Tom Lane? > >BTW, the information that the connection timed out when on separate >networks seems to be >around 5 minutes according to the place where the time out was being >experienced. > > > >To: "Peter V. Cooper" <pvcooper@gte.net> > >cc: pgsql-bugs@postgresql.org > >Subject: Re: [BUGS] New to Postgresql - Backend timeout /JDBC > >Comments: In-reply-to "Peter V. Cooper" <pvcooper@gte.net> > > message dated "Thu, 31 Jan 2002 09:40:58 -0800" > >Date: Thu, 31 Jan 2002 12:57:56 -0500 > >From: Tom Lane <tgl@sss.pgh.pa.us> > > > >"Peter V. Cooper" <pvcooper@gte.net> writes: > > > 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. > > > >If true (which I doubt), the problem is *entirely* on the client side. > >Neither the backend nor the TCP transport layer would have the > >slightest difficulty with this scenario. The KEEPALIVE timeout I > >mentioned would only come into play given an hour-long connectivity > >failure of your LAN, or a system-level crash of your client machine, > >neither of which are likely to result from a user taking a lunch break. > > > >However, I'm not very familiar with Java and so I cannot dismiss the > >possibility that some layer inside the JVM might take it upon itself to > > >close an open TCP connection after a period of inactivity. > > > >If you are able to reproduce a problem of this sort then you need to be > > >looking inside the Java code. The backend is not causing it. > > > >The pgsql-jdbc list might be a better place to ask if anyone knows of > >such problems in a JDBC context. Not sure how many JDBC people read > >pgsql-bugs. > > > > regards, tom lane > > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org