Re: libpq - check PGconn* - is it valid

On Wed, October 11, 2006 22:33, Dmitriy Chumack wrote:

> I have such a problem. My application, some server, use libpq librarry
> to interact with PostgreSQL-8.1.0 database. Postgresql server and my
> server work on different computers in the local network. When my server
> starts, it creates pool of 10 connections to database. My server use
> PQexec() to submit queries to db. But when there is no network
> connection to the database (I simply unplug the network cable from
> computer on which PG server is run) and my server calls PQexec() -
> than it hangs in PQexec() call. And it will hang there for a very
> long time (I wait about 30 minutes and there is no result). And
> when I plug in the network cable back, PQexec() remains hanging (I
> wait about 10 minutes and there is no result too).

This has come up before. At least part of the problem is likely to be
with TCP itself. When packets are lost, your operating system is
naturally disposed to: (1) assume that the problem may be congestion
somewhere on the connection, and avoid reacting in ways that may make the
congestion worse; and (2) keep trying to get the job done for as long as
it reasonably can.

If that is not acceptable for this situation, you can either:

A. Tweak your system's TCP parameters to be a bit more pessimistic about
lost packets. Be careful, though--you may get more errors with all sorts
of networking applications on the same machine, where normally they'd just
keep retrying.

B. Use non-blocking libpq calls and build your own timeouts into your
application. This is a bit more work.

> So how can I check PGconn pointer if it represent a health connection
> to prevent my server to hang in PQexec() call?

You call PQstatus() with your connection as the argument. It will return
CONNECTION_OK if the connection is still healthy.

There used to be a problem with some libpq versions, however, and you may
just be using the very last version that had this problem. These versions
would treat network failures of certain kinds (including timeouts) as
transient errors.

If your version has this bug, then PQstatus() will still return
CONNECTION_OK after you get a timeout error. If that happens to you, you
should upgrade your libpq version. There's no need to change your
database; just the libpq version that you use with your application.

Jeroen