Thread: libpq - check PGconn* - is it valid
Hi everyone. 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). I use simply (not ssl) connection to db. So how can I check PGconn pointer if it represent a health connection to prevent my server to hang in PQexec() call? Thanks in advance. -- Best regards,Dmitriy Chumack mailto:saint@apriorit.com Software Developer of ApriorIT
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
On Wednesday 11 October 2006 11:33 am, Dmitriy Chumack <saint@apriorit.com> thus communicated: > Hi everyone. > > 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). > > I use simply (not ssl) connection to db. > > So how can I check PGconn pointer if it represent a health connection > to prevent my server to hang in PQexec() call? > > Thanks in advance. Use: ConnStatusType PQstatus(const PGconn *conn); See the docs for details. -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 363-4719 terry@turbocorp.com www.turbocorp.com