Our Blog

Ongoing observations by End Point people

DBD::Pg: one ping to rule them all

By Greg Sabino Mullane · Wednesday, January 7, 2015

Tags: database, dbdpg, postgres

How can you tell if your database connection is still valid? One way, when using Perl, is to use the ping() method. Besides backslash-escaped placeholders, a revamped ping() method is the major change in the recently released version 3.5.0 of DBD::Pg, the Perl/DBI interface to Postgres. Before 3.5.0, there was a chance of false positives when using this method. In particular, if you were inside of a transaction, DBD::Pg did not actually attempt to contact the Postgres backend. This was definitely an oversight, and DBD::Pg now does the right thing.

Detecting a dead backend is a little trickier than it sounds. While libpq stores some state information for us, the only way to be sure is to issue a command to the backend. Additionally, we check the value of PQstatus in case libpq has detected a problem. Realistically, it would be far better if the Postgres protocol supported some sort of ping itself, just a simple answer/response without doing anything, but there is nothing like that yet. Fortunately, the command that is issued, /* DBD::Pg ping test, v3.5.0 */, is very lightweight.

One small side effect is that the ping() method (and its stronger cousin, the pg_ping() method) will both cancel any COPY that happens to be in progress. Really, you should not be doing that anyway! :) Calling the next copy command, either pg_getline() or pg_putline(), will tell you if the connection is valid anyway. Since the copy system uses a completely different backend path, this side effect is unavoidable.

Even this small change may cause some problems for applications, which relied on the previous false positive behavior. Leaving as a basic no-op, however, was not a good idea, so check if your application is using ping() sanely. For most applications, simple exception handling will negate to use ping() in the first place.