The Perl DBI interface to Postgresql, module DBD::Pg, has a ping()
method that is suppose to determine if a database connection is alive.
It can be seen here (see: dbd_db_ping):
http://search.cpan.org/src/DBDPG/DBD-Pg-1.43/dbdimp.c
It pings by calling:
status = _result(imp_dbh, "SELECT 'DBD::Pg ping test'");
This fails when a transaction fails -- for example when doing a
serialized transaction and another session preforms an update between
the serialized transaction's SELECT and UPDATE. In this situation no
SELECTS are allowed until a ROLLBACK.
In Perl, this failure of Ping results in a new database connection
being created, even though the connection is still valid.
I'm about to post a bug report on DBD::Pg, but I'm wondering if anyone
here could suggest a better way to implement ping() that doesn't fail
just because Postgresql is not allowing SELECTS.
What I did in my code was if ping fails, call rollback and then try
ping one more time. But, I'm not clear if that works in a more
general case or what might happen if the connection really is broken.
--
Bill Moseley
moseley@hank.org