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

Joey Smith wrote:
> On Tue, Mar 30, 2010 at 02:48:47PM -0700, Christopher Jones wrote:
>   
>> Joey Smith wrote:
>>     
>>> On Tue, Mar 30, 2010 at 05:50:29PM +0200, Marc Barilley wrote:
>>>       
>>>> This is something I'd like to add in the PostgreSQL driver. In order not
>>>> to break anything in the current implementation, my idea was to add a
>>>> driver specific constant (named PGSQL_ATTR_DEEP_CONNECTION_CHECK for
>>>> example, or anything that fits) that would make the driver run a "SELECT
>>>> 1" when retrieving a connection link from the pool to re-use it: if the
>>>> connection is actually broken, it would then be re-opened by the
>>>> subsequent code. The default would be keeping the way it works now.
>>>> Does this seem reasonnable?
>>>>
>>>> Thank you for your comments.
>>>>         
>>> I'm +1 on the idea, and suggest 'PDO::ATTR_PGSQL_PING' as the constant name.
>>>
>>>       
>> -1 on a PG specific attribute.  But +0 on a generic PDO solution.
>>
>> The approach gives an appearance of reliability and removes
>> scalability.
>>
>> Ultimately any SQL statement that actually uses a connection must
>> check for failures, because the network or database might have failed
>> in the time between the PHP connect call and the execution of the
>> statement.
>>
>> For reference, OCI8 has a parameter oci8.ping_interval.  From php.ini:
>>
>>   ; Connection: The number of seconds that must pass before issuing a
>>   ; ping during oci_pconnect() to check the connection validity. When
>>   ; set to 0, each oci_pconnect() will cause a ping. Using -1 disables
>>   ; pings completely.
>>   ; http://php.net/oci8.ping-interval
>>   ;oci8.ping_interval = 60
>>
>> I generally recommend setting it to -1, detecting SQL errors, and then
>> reconnecting if appropriate.
>>
>> Chris
>>     
>
> Perhaps I misunderstood - I thought the underlying problem he was trying to
> fix is that in certain cases, PQstatus() doesn't throw an error, even though
> the connection has actually gone away?
>
> If that's true, we definitely should do SOMETHING to fix it - if you can't
> know that the connection went away, it's pretty hard to handle that on the
> userspace side.
>
> As for it being PgSQL specific, I could go either way, I suppose - PDO::ATTR_PING_CONNECTION?
>   

The problem occurs when a connection gets recycled from the pool: from 
the user point of view, the ressource seems perfectly viable while it is 
not because it may have been dropped while it was not in use. In my 
opinion, this is a failure because the API should at least guaranty that 
what it returns is ok at the time it is called. If the connection is 
dropped sometimes later then it is the programer responsability to 
reconnect. But at the time of the PDO constructor call, I think that it 
is PDO's to construct a valid object, immediatly usable.
For example, in my case, we connect to a bunch of pg_bouncers behind a 
vip doing round-robin. Sometimes, when the overall load of the system 
decreases for a while, some connections may be closed and left in an 
"unclean" state by one of the equipements in the middle: from the front 
pov (running the php code), the connection appears to be still valid 
while it actually is closed. In that case, PQstatus() won't report an 
error. But sending something over the connection, like a "SELECT 1", 
before checking the status will raise the error. Having PDO handle this 
would make my life easier when coding my PHP :-)
I think one of the goals of PDO is to make some kind of magic and check 
this sort of thing behind the scene. This is already done for example in 
the PDO MySQL driver that calls mysql_ping() in its 
pdo_mysql_check_liveness() function, or in the PostgreSQL extension in 
php_pgsql_do_connect() if the auto_reset_persistent option is set.
What I propose seems equivalent to what already exists elsewhere. And 
fairly simple to implement. Now, I think it should be a driver specific 
constant because the functionnality is already implemented elsewhere. 
And that would not interfere with OCI8 (that I don't know much of, 
sorry...) when such a mechanism is implemented depending on a timer, or 
any other driver that would handle the auto-reconnection in its own way.

Marc