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

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?