Postgres Pro
Downloads
Home   >   mailing lists

Thread: an efficient way of checking if the connection to a db is actually open

an efficient way of checking if the connection to a db is actually open

From
Assad Jarrahian
Date:
Hi All,
   Trying to check if a connection is actually open every time I perform an operation (if not, I want to reopen it and perform that operation). isClosed()
is no good, as in, it only guarantees true if close() was actually called on it.

The documentation says, the only real way is to actually call an sql statement on it. That seems inefficient. Is there any other way that is recommended?
Much thanks in advance.

-assad

Re: an efficient way of checking if the connection to a db

From
Kris Jurka
Date:

On Thu, 15 Dec 2005, Assad Jarrahian wrote:

>   Trying to check if a connection is actually open every time I perform
> an operation (if not, I want to reopen it and perform that operation).
> isClosed()  is no good, as in, it only guarantees true if close() was
> actually called on it.
>
> The documentation says, the only real way is to actually call an sql
> statement on it. That seems inefficient. Is there any other way that is
> recommended?

The overhead of a query like "SELECT 1" can't be that much.  If this is a
concern you could add some code to the V3 QueryExecutor that issued just a
Sync message which would tell you if the connection was still good.  Then
you'd have to call a pg specific interface to access this functionality.

Getting the official driver to provide this interface would require
demonstrating that the overhead of SELECT 1 is significantly higher than
Sync.

Kris Jurka

Re: an efficient way of checking if the connection to a db is actually open

From
Paul Thomas
Date:
On 15/12/2005 20:47 Assad Jarrahian wrote:
> The documentation says, the only real way is to actually call an sql
> statement on it. That seems inefficient. Is there any other way that is
> recommended?

Something like SELECT now() would be quick as doesn't involve any database
access.

--
Paul Thomas
+------------------------------+-------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for Business           |
| Computer Consultants         | http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+-------------------------------------------+

Re: an efficient way of checking if the connection to a db

From
Tom Lane
Date:
Kris Jurka <books@ejurka.com> writes:
> Getting the official driver to provide this interface would require
> demonstrating that the overhead of SELECT 1 is significantly higher than
> Sync.

It probably is.  However, sending an empty query string to the backend
to execute ought to be competitive with a bare Sync.  libpq supports
that; does JDBC?

            regards, tom lane

Re: an efficient way of checking if the connection to a db

From
Kris Jurka
Date:

On Thu, 15 Dec 2005, Tom Lane wrote:

> It probably is.  However, sending an empty query string to the backend
> to execute ought to be competitive with a bare Sync.  libpq supports
> that; does JDBC?

You can say Statement.execute(""), but that still does the full extended
query protocol setup:

14:25:48.375 (1)  FE=> Parse(stmt=null,query="",oids={})
14:25:48.377 (1)  FE=> Bind(stmt=null,portal=null)
14:25:48.378 (1)  FE=> Describe(portal=null)
14:25:48.378 (1)  FE=> Execute(portal=null,limit=0)
14:25:48.378 (1)  FE=> Sync
14:25:48.379 (1)  <=BE ParseComplete [null]
14:25:48.380 (1)  <=BE BindComplete [null]
14:25:48.380 (1)  <=BE NoData
14:25:48.380 (1)  <=BE EmptyQuery
14:25:48.381 (1)  <=BE ReadyForQuery(I)

Testing this with the V2 protocol reveals an issue, but it looks like
the server's fault.

14:33:42.550 (1)  FE=> Query("")
14:33:42.550 (1)  <=BE EmptyQuery

At this point the driver is waiting for ReadyForQuery, but the server is
waiting for another query.  The documentation states, "If a completely
empty (no contents other than whitespace) query string is received, the
response is EmptyQueryResponse followed by ReadyForQuery."

Kris Jurka

Re: an efficient way of checking if the connection to a db

From
Tom Lane
Date:
Kris Jurka <books@ejurka.com> writes:
> Testing this with the V2 protocol reveals an issue, but it looks like
> the server's fault.

> 14:33:42.550 (1)  FE=> Query("")
> 14:33:42.550 (1)  <=BE EmptyQuery

> At this point the driver is waiting for ReadyForQuery, but the server is
> waiting for another query.

I think you've got a problem on the driver side.
Here, strace'ing a program doing PQexec(conn, "") shows:

17:53:33.677241 send(3, "Q\0\0\0\5\0", 6, 0) = 6
...
17:53:33.678085 recv(3, "I\0\0\0\4Z\0\0\0\5I", 16384, 0) = 11

which looks like EmptyQueryResponse followed by ReadyForQuery to me.

            regards, tom lane

Re: an efficient way of checking if the connection to a db

From
Kris Jurka
Date:

On Thu, 15 Dec 2005, Tom Lane wrote:

> I think you've got a problem on the driver side.
> Here, strace'ing a program doing PQexec(conn, "") shows:
>
> 17:53:33.677241 send(3, "Q\0\0\0\5\0", 6, 0) = 6
> ...
> 17:53:33.678085 recv(3, "I\0\0\0\4Z\0\0\0\5I", 16384, 0) = 11
>
> which looks like EmptyQueryResponse followed by ReadyForQuery to me.

This was indeed a driver problem.  With V2 protocol EmptyQuery is followed
by a single null byte, but the driver was expecting a four byte message
length, like the V3 protocol sends.

Fixed in 8.0, 8.1, and head.

Kris Jurka

Re: an efficient way of checking if the connection to a db is actually open

From
Guillaume Cottenceau
Date:
Assad Jarrahian <jarraa 'at' gmail.com> writes:

> Hi All,
>    Trying to check if a connection is actually open every time I perform an
> operation (if not, I want to reopen it and perform that operation).

Why not performing the operation anyway, and when it fails
reopening the connection and retrying the operation?

Seems that it's the idea the JDBC designers had in mind, which
doesn't seem too stupid.

--
Guillaume Cottenceau