How to "ping" the database

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

How to "ping" the database

Bill Moseley
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
[hidden email]


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: How to "ping" the database

Tom Lane-2
Bill Moseley <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: How to "ping" the database

Bill Moseley
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
[hidden email]


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly