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

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

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