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