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(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)
> >
>