Re: [PDO] PostgreSQL auto-reconnect in PDO contructor for permanent connections: request for comments

This thread started out as a bug report with the liveness check in the  
postgres driver; the mechanism I'm using in an internal project, which  
is similar in architecture to PDO, is this, which has the advantage of  
not being a blocking operation--I've seen the postgres API block  
indefinitely in some stale/retired connection cases.  This code will  
give 5 attempts up to 5 seconds each to determine if the connection is  
still viable.

--Wez.

   int tries = 5;
   PGresult *res;
   struct pollfd pfd;
   GET_DRV(drv);

   PQsendQuery(D->server, "SELECT 1;");

   while (tries--) {
     res = PQgetResult(D->server);
     if (res) {
       break;
     }
     pfd.fd = PQsocket(D->server);
     pfd.events = POLLIN|POLLHUP|POLLERR;
     poll(&pfd, 1, 5000);
   }
   if (res && PQstatus(D->server) == CONNECTION_OK) {
     PQclear(res);
     return 1;
   }
   if (res) {
     PQclear(res);
   }
   PQreset(D->server);
   return 0;