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

Christopher Jones a écrit :
>
>
> 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
>

I went to the wiki and saw this paragraph about persistent connections,
proposing to allow to disable certain connection reset/cleanup features.
Then maybe the approach should be the opposite than what I proposed:
always perform a "SELECT 1" on the connection and add a driver specific
constant to disable this. This is a fair proposal, considering that the
average programmer expects such magic from the driver while the expert
one, namely the one who targets performance and the rest, has a better
knowledge of his tools and may -- or may not -- want to use this
feature. Then the default behaviour would be that "PDO::ATTR_PGSQL_PING"
be set to true (or the driver would behave like so) and one could
disable this feature by setting the option to false. I could even add
this mechanism to the MySQL driver, when I have time.
Maybe OCI8 could use a "PDO::ATTR_OCI_PING_INTERVAL" or something that
would set the time interval between pings? That way, both drivers could
implement their own way of handling connections recycling without
interfering with each other. But using a time interval in the PostgreSQL
driver is beyond the scope of the modification I propose...

Marc