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

On 03/30/2010 04:01 PM, 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?

That's very similar to the case that oci8.ping_interval was created
for.  Persistent connections (oci_pconnect) can be held open for days.
If the DB is restarted (invalidating the connection data structure
cached in PHP) the only time PHP knows about the restart is when the
first SQL statement generates an error.  In this scenario setting
oci8.ping_interval would allow PHP OCI8 to detect and reconnect
transparently so the script wouldn't see an error.  This has the
availability & scalability caveats I mentioned previously.

Recent OCI8 code handles this better and can reconnect automatically
in some cases without the overhead of a ping, but that's getting off
topic.

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

It would be more flexible, aka better for other drivers, to allow a
variable time interval.

Chris

-- 
Email: [email protected]
Tel:  +1 650 506 8630
Blog:  http://blogs.oracle.com/opal/
Free PHP Book: http://tinyurl.com/ugpomhome