2

I am trying to check connection status with PostgreSQL server with PQstatus and it always always return CONNECTION_OK even when the cable to the server is unplugged.

user1972556
  • 21
  • 2

1 Answers1

4

Assuming your question is "why does it happen", and the answer "it's expected behavior" is not enough.

The documentation of PQstatus doesn't promise to test the connection, e.g. by sending anything to server and waiting for the answer. It just returns connection status as it's known by the client library. This status can change if (1) something happens to the connection, (2) the client library has a chance to notice it.

Send something like SELECT 1 to server periodically, if you want to notice when the cable is unplugged. UPD: there is also a pg_ping function in libpq, which does the same thing without a need for senseless query.

Even for underlying TCP layer, unplugging the cable generally doesn't break connections. The system never knows whether it's going to be plugged back, so it would be unwise. There is a SO_KEEPALIVE option, but it normally doesn't consider connection "broken" too early (e.g. the default on Linux is to send keepalive probes after 3 hours of inactivity).

And even if the connection is broken, libpq should actually attempt to read or write the socket to detect it as error. This won't happen until you make it happen somehow -- e.g. by executing a query as advised above.

Anton Kovalenko
  • 20,999
  • 2
  • 37
  • 69
  • 1
    You can also just send an empty string as a query. But there's one more important part of this - the idea of testing connections is fairly silly, since the connection might die between when you test it and when you run your "real" query. Don't test connections, just use them, and if they fail be prepared to retry everything since you opened the transaction. – Craig Ringer Jan 14 '13 at 02:59