Client query hangs when network connection is lost to the server.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Client query hangs when network connection is lost to the server.

Steve Rosenberry
Call me stupid if you must, but I've tried both tcp_keepalive_xxx parameters and PQcancel() to abort a query to a remote server to which I've lost the network connection with no change in results.  It appears to take about 15 minutes before the query gives up.  I assume that must be due to some other system parameter that I cannot find.  I can be very happy if I can shorten the 15 minute delay to about 30 to 60 seconds or have something else get me out the query in which I am stuck.

Specifics below.  Anything you need to know, just ask...

Thanks,
Steve


Running RedHat Enterprise Linux 5.x with PostgreSQL 8.4.


My system TCP keep alive parameters are:

/proc/sys/net/ipv4/tcp_keepalive_time  15
/proc/sys/net/ipv4/tcp_keepalive_intvl  5
/proc/sys/net/ipv4/tcp_keepalive_probes  5

My postgresql.conf file has identical parameters:

tcp_keepalives_idle = 15                # TCP_KEEPIDLE, in seconds;
tcp_keepalives_interval = 5             # TCP_KEEPINTVL, in seconds;
tcp_keepalives_count = 3                # TCP_KEEPCNT;


The code I used to cancel the query was:

      char errBuf[ 256 ];
      PGcancel* pgCancel = PQgetCancel( pConn );
      if( pgCancel != NULL )
      {
         int rc = PQcancel( pgCancel, errBuf, sizeof( errBuf ) );
         PQfreeCancel( pgCancel );
         cout << ": rc[" << rc << "] errBuf[" << errBuf << "]" << endl );
      }

which returned the following:

rc[0] errBuf[PQcancel() -- connect() failed: No route to host]

I know I have no connection!  The ping to the server failed, thus I called PQCancel() to get the query to return, so I could continue on about my business.







Reply | Threaded
Open this post in threaded view
|

Re: Client query hangs when network connection is lost to the server.

Andrej Ricnik-Bay
On 9 April 2013 16:48, Steve Rosenberry
<[hidden email]> wrote:
>
> Call me stupid if you must, but I've tried both tcp_keepalive_xxx parameters and PQcancel() to abort a query to a remote server to which I've lost the network connection with no change in results.  It appears to take about 15 minutes before the query gives up.  I assume that must be due to some other system parameter that I cannot find.  I can be very happy if I can shorten the 15 minute delay to about 30 to 60 seconds or have something else get me out the query in which I am stuck.
>
> Specifics below.  Anything you need to know, just ask...

If I understand it correctly PQgetCancel operates against the server
side.  So if you lost network connectivity
to the server the cancel won't make it there?


Cheers,
Andrej


--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Reply | Threaded
Open this post in threaded view
|

Re: Client query hangs when network connection is lost to the server.

Steve Rosenberry
In reply to this post by Steve Rosenberry
For others who may stumble upon this looking for an answer.  This seems to be working as expected.

Another person at work found sample code showing how to mimic the PQexec() functionality such that one has a chance to break out of waiting for the server response.  The new code uses the PQsendQuery(), PQisBusy(), PQconsumeInput(), and PQgetResult() functions and is based upon example code from page 342 of "PostgreSQL: A Comprehensive Guide to Building, Programming, and Administering PostgreSQL Databases" by Korry Douglas and Susan P. Douglas, 1st Edition, February 2003.

Basically I added a flag to the wait loop that indicates the query should be cancelled, breaks the loop, and returns a NULL PGresult*.  The flag is set by an independent mechanism
(in our simplistic case, a simple system ping) that determines the PostgreSQL server is no longer available through the network.