Re: PQstatus() detect change in connection... - Mailing list pgsql-hackers

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