Postgres libpq how to tell if a connection has been terminated

Question

Given the following libpq code:

PGconn * internalConnection = PQconnectdb("my connection string");
if (PQstatus(internalConnection) != CONNECTION_OK)
{
   // return error on failure
}

// Kill all connections
if(0 == PQsendQuery(internalConnection, "SELECT pid, (SELECT pg_terminate_backend(pid)) as killed from pg_stat_activity"))
{
   // return error on failure
}

Which does the following:

1. Makes a connection to a server.
2. Executes a query, killing all of the connections (including this connection).

How can I tell via the client side that the connection has been killed? If I run the following again:

PQstatus(internalConnection)

I still gets CONNECTION_OK as my result.

Using the MySQL API I can check mysql_ping on a connection to see if a connection is still open, but on Postgres, there does not seem to be a similar method call (that I could find).

Any suggestions as to how I can determine from the client side if a connection has been killed?

Answer 1

To see if a connection appears to be alive via libpq you can send an empty query string:

res = PQexec(conn, "");

and test the result.

At the PostgreSQL protocol level it can be done more efficiently with a simple Sync message, but libpq does not currently expose a way to send just a Sync.

---

You shouldn't have to do that though. When the connection is closed by the server it sends a TCP RST to the client. This should inform the client that its socket has closed. While I haven't tested it, I suspect that calling PQconsumeInput will be sufficient to process any remaining data on the connection and notice that the socket has been closed. Note, though, that if there's no input to consume then this function will block waiting for a message from the server.

---

BTW, while in this case it's reasonable(ish) to test the connection, in general that's a very bad design. You should never do this:

• Test connection
• Run query
• Assume success

as there's a race between testing the connection and actually executing the query or queries. Not to mention the fact that queries can fail for other reasons.

Your application should always be able to handle a transaction failing, and be able to retry it. You should have logic in place to abort and retry just the transaction on transient errors, or to reconnect if the connection its self is bad.

PostgreSQL's libpq could make this a lot easier than it currently does for users. It should expose some functions to test the SQLState to see if it's possibly transient, for example, so apps can use a single call to retry on deadlock aborts, serialization failures, and so on. For now you have to do this yourself.