Re: After server restart I get - An I/O error occured while sending to the backend.

On 22/05/2010 3:31 PM, Rod wrote:
> I find it hard to believe! We live in 21st century, year 2010.
> Electric cars and black president. And there's still no connection
> pool library that keeps track if connection is valid or not?

Sure, there are plenty of connection pools that *try*.

It's fundamentally impossible to do it properly, though, as the
connection can die at *any* time. Including between when the connection
pool tests it and when it hands it to the application for use. This is a
race condition that you just can't solve.

For that matter, the connection can die while the app is using it -
someone trips over an Ethernet cable (or a server's power cable), a
laptop goes out of wifi range, whatever.

So - a connection pool testing for connection validity is only a
convenience. The app must still be prepared to handle the connection
breaking at any time. Given that, why bother testing it with the
connection pool? It only offers a false sense of security, and wastes
resources polling unused connections.

I suppose it'd be possible to produce a Connection object wrapper that
recorded all operations sent through it and if the underlying connection
dropped replayed those operations to hide the connection loss from the
application. However, the new connection couldn't obtain the same
snapshot of the database state as the old one so the replay couldn't
guarantee it'd have the same effects as the original run of those same
statements. It'd be extremely unsafe at best.

Now, maybe if the database kept the snapshot and connection state for
lost connections around in case the client re-connected to pick up where
it left off, then it'd be possible*. Hell, the database could even save
that state and restore it after a database server reload. The complexity
involved would be really ugly, though, and it'd only be of use to poorly
behaved apps that can't gracefully handle connection failure. Even then
it wouldn't save you from unexpected situations like server power loss,
postgresql backend/server unexpected termination, etc, so the app/pool
STILL needs to be able to gracefully cope with having all its work
yanked out from under it.

This is one of the reasons client/server transactional SQL programming
can be more complicated than everything-on-one-machine shared file
database programming. On the other hand, that could go wrong too, just
not as easily so people pretended it couldn't and hoped it never would.

--
Craig Ringer