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


Wed, 17 Oct 2001 20:47:09 -0400
Matthew Hagerty <mhagerty@voyager.net>
PQstatus() detect change in connection...
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@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Matthew Hagerty
> Sent: Thursday, 18 October 2001 10:47 AM
> To: pgsql-hackers@postgresql.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@postgresql.org)
>
Thu, 18 Oct 2001 11:51:17 +1000
"Mark Pritchard" <mark@tangent.net.au>
Re: PQstatus() detect change in connection...
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@postgresql.org
> > [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Matthew Hagerty
> > Sent: Thursday, 18 October 2001 10:47 AM
> > To: pgsql-hackers@postgresql.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@postgresql.org)
> >
>


Thu, 18 Oct 2001 00:19:05 -0400
Matthew Hagerty <mhagerty@voyager.net>
Re: PQstatus() detect change in connection...
Matthew Hagerty <mhagerty@voyager.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

Thu, 18 Oct 2001 14:10:19 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: PQstatus() detect change in connection... 
At 02:10 PM 10/18/2001 -0400, Tom Lane wrote:
>Matthew Hagerty <mhagerty@voyager.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


Thu, 18 Oct 2001 16:54:57 -0400
Matthew Hagerty <mhagerty@voyager.net>
Re: PQstatus() detect change in connection... 
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@gmx.net   http://funkturm.homeip.net/~peter


Thu, 18 Oct 2001 23:03:23 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: PQstatus() detect change in connection...
Matthew Hagerty <mhagerty@voyager.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

Thu, 18 Oct 2001 20:51:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: PQstatus() detect change in connection... 