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

Honestly, the driver is broken if it does not correctly detect a dead  
connection, or if it blocks indefinitely.  It doesn't really matter  
what the user wants in that case; what they get is unusable without  
manually taking the same steps in their code.

The postgres API is blocking by default.  You can configure a timeout  
in your connection string, but this does not apply to all cases (only  
connection time, IIRC).  In all other cases, the calls can block, and  
can block indefinitely depending on the nature of the disconnect, the  
locality of the server, the nature of the network hardware between the  
client and the server.

--Wez.

On Mar 31, 2010, at 7:25 PM, Marc Barilley wrote:

> I don't think this is a bug per se, just a missing feature. Some  
> programmers may want a thorough check of the connection while some  
> may not. Both have their benefits and drawbacks. I think it is up to  
> the programmer to choose if the connection needs to be checked or not.
> I'm curious to know if you have more precise informations about the  
> conditions when the API can block. This could help :) Thank you.
>
> Marc
>
>
> Wez Furlong a écrit :
>> 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;
>>
>>
>>
>