Re: PQstatus() detect change in connection...

Greetings,

PostgreSQL 7.1.3, FreeBSD-4.3-RELEASE, gcc 2.95.3

I'm trying to attempt to detect a failed backend connection, but a call to
PQstatus() always returns the state of the backend when the call was
made. For example, take this test code:

PGconn *pgConn;
PGresult *pgRes;
int fdPGconn;

int i = 0;
int iNewState = 0;
int iOldState = 60;

pgConn = PQconnectdb("dbname=pglogd user=postgres");

while ( i == 0 )
{
iNewState = PQstatus(pgConn);

if ( iNewState != iOldState )
{
iOldState = iNewState;
printf("Connection State [%d]\n", iNewState);

fdPGconn = PQsocket(pgConn);
printf("Connection Socket [%d]\n", fdPGconn);
}

sleep(1);
}

PQfinish(pgConn);

If you start this with the backend running, the status is CONNECTION_OK,
then pull the plug on the backend, the call to PQstatus() will still return
CONNECTION_OK, even though the backend is not running. Start this program
with the backend not running, then start the backend, PQstatus() never sees
the backend come to life...

Am I reading PQstatus() wrong? Is there any way to detect when the backend
goes down or comes back up?

Thanks,
Matthew

I presume you are trying to re-establish a connection automatically...if
that doesn't apply, ignore the rest of this email :)

The way I interpreted the docs was that you can use the return codes from
PQexec() to establish whether the command was sent to the backend correctly.
PQresultStatus() returns whether the command was syntactically
correct/executed OK.

I've attached a chunk of code from a back-end independent DB driver
(supports Oracle, PgSQL, MySQL through the same front end API), which
implements this auto-reconnect. Take a look at the sqlExec() method.

This code successfully recovers when used in a client connection pool in the
following sequence:

1) start postmaster
2) connect through pool/driver
3) issue SQL statements
4) kill postmaster
5) start postmaster
6) issue SQL statements
7) driver detects connection invalid, reconnects and re-issues
automatically.

Perhaps those infinitely more knowledgeable on the list have a better/more
correct way of doing things?

Cheers,

Mark Pritchard

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org]On Behalf Of Matthew Hagerty
> Sent: Thursday, 18 October 2001 10:47 AM
> To: pgsql-hackers(at)postgresql(dot)org
> Subject: [HACKERS] PQstatus() detect change in connection...
>
>
> Greetings,
>
> PostgreSQL 7.1.3, FreeBSD-4.3-RELEASE, gcc 2.95.3
>
> I'm trying to attempt to detect a failed backend connection, but
> a call to
> PQstatus() always returns the state of the backend when the call was
> made. For example, take this test code:
>
> PGconn *pgConn;
> PGresult *pgRes;
> int fdPGconn;
>
> int i = 0;
> int iNewState = 0;
> int iOldState = 60;
>
> pgConn = PQconnectdb("dbname=pglogd user=postgres");
>
> while ( i == 0 )
> {
> iNewState = PQstatus(pgConn);
>
> if ( iNewState != iOldState )
> {
> iOldState = iNewState;
> printf("Connection State [%d]\n", iNewState);
>
> fdPGconn = PQsocket(pgConn);
> printf("Connection Socket [%d]\n", fdPGconn);
> }
>
> sleep(1);
> }
>
> PQfinish(pgConn);
>
> If you start this with the backend running, the status is CONNECTION_OK,
> then pull the plug on the backend, the call to PQstatus() will
> still return
> CONNECTION_OK, even though the backend is not running. Start
> this program
> with the backend not running, then start the backend, PQstatus()
> never sees
> the backend come to life...
>
> Am I reading PQstatus() wrong? Is there any way to detect when
> the backend
> goes down or comes back up?
>
> Thanks,
> Matthew
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

I am trying to re-establish a connection, however, I cannot afford to issue
a query to determine if the connection still exists. I'm writing a server
that uses the asynchronous query processing functions and speed is an
issue. Queries are slow compared to what the server does and it cannot
wait around for a query to finish just to see if another query *should* be
attempted based on the connection status.

I've been digging into the libpq code to see what is going on, maybe I can
gleam a little hint or two there... Anyone know a good *fast* way to test
if a socket is still valid?

Thanks,
Matthew

At 11:51 AM 10/18/2001 +1000, Mark Pritchard wrote:
>I presume you are trying to re-establish a connection automatically...if
>that doesn't apply, ignore the rest of this email :)
>
>The way I interpreted the docs was that you can use the return codes from
>PQexec() to establish whether the command was sent to the backend correctly.
>PQresultStatus() returns whether the command was syntactically
>correct/executed OK.
>
>I've attached a chunk of code from a back-end independent DB driver
>(supports Oracle, PgSQL, MySQL through the same front end API), which
>implements this auto-reconnect. Take a look at the sqlExec() method.
>
>This code successfully recovers when used in a client connection pool in the
>following sequence:
>
>1) start postmaster
>2) connect through pool/driver
>3) issue SQL statements
>4) kill postmaster
>5) start postmaster
>6) issue SQL statements
>7) driver detects connection invalid, reconnects and re-issues
>automatically.
>
>Perhaps those infinitely more knowledgeable on the list have a better/more
>correct way of doing things?
>
>Cheers,
>
>Mark Pritchard
>
> > -----Original Message-----
> > From: pgsql-hackers-owner(at)postgresql(dot)org
> > [mailto:pgsql-hackers-owner(at)postgresql(dot)org]On Behalf Of Matthew Hagerty
> > Sent: Thursday, 18 October 2001 10:47 AM
> > To: pgsql-hackers(at)postgresql(dot)org
> > Subject: [HACKERS] PQstatus() detect change in connection...
> >
> >
> > Greetings,
> >
> > PostgreSQL 7.1.3, FreeBSD-4.3-RELEASE, gcc 2.95.3
> >
> > I'm trying to attempt to detect a failed backend connection, but
> > a call to
> > PQstatus() always returns the state of the backend when the call was
> > made. For example, take this test code:
> >
> > PGconn *pgConn;
> > PGresult *pgRes;
> > int fdPGconn;
> >
> > int i = 0;
> > int iNewState = 0;
> > int iOldState = 60;
> >
> > pgConn = PQconnectdb("dbname=pglogd user=postgres");
> >
> > while ( i == 0 )
> > {
> > iNewState = PQstatus(pgConn);
> >
> > if ( iNewState != iOldState )
> > {
> > iOldState = iNewState;
> > printf("Connection State [%d]\n", iNewState);
> >
> > fdPGconn = PQsocket(pgConn);
> > printf("Connection Socket [%d]\n", fdPGconn);
> > }
> >
> > sleep(1);
> > }
> >
> > PQfinish(pgConn);
> >
> > If you start this with the backend running, the status is CONNECTION_OK,
> > then pull the plug on the backend, the call to PQstatus() will
> > still return
> > CONNECTION_OK, even though the backend is not running. Start
> > this program
> > with the backend not running, then start the backend, PQstatus()
> > never sees
> > the backend come to life...
> >
> > Am I reading PQstatus() wrong? Is there any way to detect when
> > the backend
> > goes down or comes back up?
> >
> > Thanks,
> > Matthew
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
> >
>

Matthew Hagerty <mhagerty(at)voyager(dot)net> writes:
> Anyone know a good *fast* way to test
> if a socket is still valid?

What exactly are you trying to defend against?

In general, I don't believe that there is any way of discovering whether
the server is still up, other than to send it a query. (FWIW, an empty
query string bounces back very quickly, with little processing.)

For particular scenarios it's possible that some notification has been
delivered to the client, but if you have had (say) a loss of network
connectivity then there just is no other alternative. Your end isn't
going to discover the connectivity loss until it tries to send a
message.

regards, tom lane

At 02:10 PM 10/18/2001 -0400, Tom Lane wrote:
>Matthew Hagerty <mhagerty(at)voyager(dot)net> writes:
> > Anyone know a good *fast* way to test
> > if a socket is still valid?
>
>What exactly are you trying to defend against?
>
>In general, I don't believe that there is any way of discovering whether
>the server is still up, other than to send it a query. (FWIW, an empty
>query string bounces back very quickly, with little processing.)
>
>For particular scenarios it's possible that some notification has been
>delivered to the client, but if you have had (say) a loss of network
>connectivity then there just is no other alternative. Your end isn't
>going to discover the connectivity loss until it tries to send a
>message.
>
> regards, tom lane

I was using PQstatus() under the assumption that it actually *checked* the
connection, however I have since discovered that is simply returns the
value in a structure, and that value only gets updated in pqReadData() or
pqReadReady() (both of which are internal function calls.)

What I'm doing is using the asynchronous processing to write a server that
does not have to wait around for a query to finish (which is a slow process
compared to what the rest of the server does.) So, using a query to test
if the connection is up seems rather redundant and slow... I was hoping to
come up with a faster more simple solution. If the connection is down I
need to write - what would have been a query - to a temporary place and
attempt a reconnect, all while going off and doing other things.

This all came about when my main select() bailed because the backend went
down and the socket's file-descriptor became invalid. I could probably
catch the error in that loop, but I also want to check the connection
*before* submitting a query... Basically, I hope to avoid a huge rewrite
based on my assumption of how PQstatus() was actually working. ;-)

Currently I'm looking at fnctl() or a dedicated select() call (similar to
what pgReadReady() does), but I'm not sure of the OS overhead of these
solutions compared to each other or an empty query. Any insight would be
greatly appreciated.

Thanks,
Matthew

Matthew Hagerty writes:

> I am trying to re-establish a connection, however, I cannot afford to issue
> a query to determine if the connection still exists.

But requesting that the server do something *is* the only way to know
whether it's still alive. Another question to ask, of course, would be,
why is your server always going down?

> I've been digging into the libpq code to see what is going on, maybe I can
> gleam a little hint or two there... Anyone know a good *fast* way to test
> if a socket is still valid?

Try to send or receive something.

--
Peter Eisentraut peter_e(at)gmx(dot)net http://funkturm.homeip.net/~peter

Matthew Hagerty <mhagerty(at)voyager(dot)net> writes:
> but I also want to check the connection
> *before* submitting a query...

This strikes me as utterly pointless. You'll need to be able to recover
from query failure anyway, so what's the value of testing beforehand?
Send the query and see if it works or not.

regards, tom lane