Re: 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(at)microolap(dot)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(at)gmail(dot)com> wrote:
> On Tue, Jul 6, 2010 at 1:08 PM, Pavel Golub <pavel(at)microolap(dot)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(at)gmail(dot)com> wrote:
>> On Tue, Jul 6, 2010 at 1:08 PM, Pavel Golub <pavel(at)microolap(dot)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(at)gf(dot)microolap(dot)com

On Thu, Jul 8, 2010 at 4:57 PM, Pavel Golub <pavel(at)microolap(dot)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(dot)fujii(at)gmail(dot)com> wrote:
> On Thu, Jul 8, 2010 at 4:57 PM, Pavel Golub <pavel(at)microolap(dot)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 Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On Thu, Jul 8, 2010 at 5:27 PM, Magnus Hagander <magnus(at)hagander(dot)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