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

I've written a wrapper that use the "setTransationIsolation" method as
first operation against a just obtained connection to implicitly check
if it is still alive.

On 05/22/2010 03:06 PM, Rod wrote:
> Tanks for the replies.
> To test the connection, what statement or command should I execute?
> Anyone have code sample?
>
> On Sat, May 22, 2010 at 7:57 PM, Craig Ringer
> <craig@postnewspapers.com.au> wrote:
>
>> 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
>>
>>
>