Обсуждение: How to "ping" the database

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

On Wed, Aug 17, 2005 at 04:25:48PM -0400, Tom Lane wrote:
> Of course, this begs the question of what ping is really supposed to
> test and when it's supposed to be allowable.  The above will not work
> if in the middle of retrieving a query result, for example.

Well, there's that.  I'm not really sure why there's a need for a ping
-- and I've heard others question it, too.

Perl's DBI has a connect_cached() function that is suppose to return a
cached connection if it's still alive.  So that is one place "ping" is
used.  If ping fails then a new connection is created.


--
Bill Moseley
moseley@hank.org

Bill Moseley <moseley@hank.org> writes:
> 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.

I think you could just send an empty query string and see if anything
comes back.

Of course, this begs the question of what ping is really supposed to
test and when it's supposed to be allowable.  The above will not work
if in the middle of retrieving a query result, for example.

            regards, tom lane