Thread: keepalive in libpq using
Hello all. While I'm very excited about enabling keepalives in libpq, I want to know how can I use this functionality in my application? Let's imagine that I connect to a server with keepalives option, other options (keepalives_idle, keepalives_interval, keepalives_count) are used either. Then network goes down. So, how will I know that connection is dead? Any callback function? Or should I check PQstatus periodically? Thank in advance -- Nullus est in vitae sensus ipsa vera est sensus.
On Tue, Jul 6, 2010 at 1:08 PM, Pavel Golub <pavel@microolap.com> wrote: > While I'm very excited about enabling keepalives in libpq, I want to > know how can I use this functionality in my application? > > Let's imagine that I connect to a server with keepalives option, other > options (keepalives_idle, keepalives_interval, keepalives_count) are > used either. Then network goes down. So, how will I know that > connection is dead? Any callback function? Or should I check PQstatus > periodically? I'm not sure, exactly. I think what'll happen is that if you're trying to read data from the remote server, the connection will eventually break instead of hanging forever, but I'm not exactly sure what that'll look like at the libpq level. I'm not sure what effect it'll have on an idle connection. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
On Wed, Jul 7, 2010 at 10:04 PM, Robert Haas <robertmhaas@gmail.com> wrote: > On Tue, Jul 6, 2010 at 1:08 PM, Pavel Golub <pavel@microolap.com> wrote: >> While I'm very excited about enabling keepalives in libpq, I want to >> know how can I use this functionality in my application? >> >> Let's imagine that I connect to a server with keepalives option, other >> options (keepalives_idle, keepalives_interval, keepalives_count) are >> used either. Then network goes down. So, how will I know that >> connection is dead? Any callback function? Or should I check PQstatus >> periodically? > > I'm not sure, exactly. I think what'll happen is that if you're > trying to read data from the remote server, the connection will > eventually break instead of hanging forever, but I'm not exactly sure > what that'll look like at the libpq level. I'm not sure what effect > it'll have on an idle connection. When network goes down while receiving result from server, you will get PGRES_FATAL_ERROR from PQresultStatus(). Also you can get the error message "could not receive data from server: Connection timed out" via PQerrorMessage(). Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
Hello, Fujii. You wrote: FM> On Wed, Jul 7, 2010 at 10:04 PM, Robert Haas <robertmhaas@gmail.com> wrote: >> On Tue, Jul 6, 2010 at 1:08 PM, Pavel Golub <pavel@microolap.com> wrote: >>> While I'm very excited about enabling keepalives in libpq, I want to >>> know how can I use this functionality in my application? >>> >>> Let's imagine that I connect to a server with keepalives option, other >>> options (keepalives_idle, keepalives_interval, keepalives_count) are >>> used either. Then network goes down. So, how will I know that >>> connection is dead? Any callback function? Or should I check PQstatus >>> periodically? >> >> I'm not sure, exactly. I think what'll happen is that if you're >> trying to read data from the remote server, the connection will >> eventually break instead of hanging forever, but I'm not exactly sure >> what that'll look like at the libpq level. I'm not sure what effect >> it'll have on an idle connection. FM> When network goes down while receiving result from server, you will FM> get PGRES_FATAL_ERROR from PQresultStatus(). Also you can get the FM> error message "could not receive data from server: Connection timed out" FM> via PQerrorMessage(). Sounds good for me. My customer proposed such a scenario: I have opened connection to database server (ver 8.4.3) through the SSH tunnel. This tunnel is created by external program "PUTTY". My PC running Application is connected to the ETH switch and server is connected to another port of the switch. So, when I disconnect server from the switch, my PC is still online (I mean ETH port have the link). So, my local side of the SSH tunnel is still alive, but remote side is down... So no connection to server is possible at this moment. But in this scenario, when I do something like this: PQExec(...); Application stay locked on this command. Looks like client is still waiting answer from the server in the case the TCP connection is still alive, even if Server is not accessible. The question is: Can this situation be solved with keealives? FM> Regards, -- With best wishes,Pavel mailto:pavel@gf.microolap.com
On Thu, Jul 8, 2010 at 4:57 PM, Pavel Golub <pavel@microolap.com> wrote: > Sounds good for me. My customer proposed such a scenario: > > I have opened connection to database server (ver 8.4.3) through the > SSH tunnel. This tunnel is created by external program "PUTTY". My PC running Application > is connected to the ETH switch and server is connected to another port of the switch. So, when > I disconnect server from the switch, my PC is still online (I mean ETH port have the link). So, my > local side of the SSH tunnel is still alive, but remote side is down... So no connection to server > is possible at this moment. But in this scenario, when I do something like this: > > PQExec(...); > > Application stay locked on this command. Looks like client is still waiting answer from the server in > the case the TCP connection is still alive, even if Server is not accessible. > > The question is: Can this situation be solved with keealives? AFAIK, keepalive works only if there is no un-Acked data pending. If network goes down before sending query to server (i.e., calling PQexec), the ACK for the query doesn't arrive, so you cannot detect the disconnection via keepalive while waiting for the result of the query. OTOH, if network goes down after sending query, keepalive would make you detect the disconnection. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
On Thu, Jul 8, 2010 at 10:23, Fujii Masao <masao.fujii@gmail.com> wrote: > On Thu, Jul 8, 2010 at 4:57 PM, Pavel Golub <pavel@microolap.com> wrote: >> Sounds good for me. My customer proposed such a scenario: >> >> I have opened connection to database server (ver 8.4.3) through the >> SSH tunnel. This tunnel is created by external program "PUTTY". My PC running Application >> is connected to the ETH switch and server is connected to another port of the switch. So, when >> I disconnect server from the switch, my PC is still online (I mean ETH port have the link). So, my >> local side of the SSH tunnel is still alive, but remote side is down... So no connection to server >> is possible at this moment. But in this scenario, when I do something like this: >> >> PQExec(...); >> >> Application stay locked on this command. Looks like client is still waiting answer from the server in >> the case the TCP connection is still alive, even if Server is not accessible. >> >> The question is: Can this situation be solved with keealives? > > AFAIK, keepalive works only if there is no un-Acked data pending. > If network goes down before sending query to server (i.e., calling > PQexec), the ACK for the query doesn't arrive, so you cannot detect > the disconnection via keepalive while waiting for the result of the > query. OTOH, if network goes down after sending query, keepalive > would make you detect the disconnection. In this scenario, wouldn't it work if the *ssh connection* had keepalives enabled though? Then that one should tear down, which in turn would send a clear signal to libpq the connection is gone. -- Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/
On Thu, Jul 8, 2010 at 5:27 PM, Magnus Hagander <magnus@hagander.net> wrote: > In this scenario, wouldn't it work if the *ssh connection* had > keepalives enabled though? Then that one should tear down, which in > turn would send a clear signal to libpq the connection is gone. Sorry, I'm not sure that. Can the ssh connection detect the disconnection via keepalive, in that case? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center