Re: keep a connection alive

On Thursday 11 September 2008 06:13:26 pm James Henstridge wrote:
> On Thu, Sep 11, 2008 at 11:33 PM, johnf <jfabiani-rLSQ/[email protected]> wrote:
> > Hi,
> > I created a program that uses psycopg2 to connect remotely to a postgres
> > 8.3.1 database (server is on a linux box).  On my local linux system I
> > can keep the connection forever.  However, on the clients XP windows it
> > appears to timeout during idle time.  It has been suggested that maybe
> > the tcp_keepalives options need to be set on the server.  Can someone
> > here enlighten me regarding how psycopg2 handles the problem on windows. 
> > And what I might want to do on the server side if anything.  The setting
> > is at default (as it came from openSUSE 11).
> >
> > The windows version of psycopg2 is 2.0.6.b1.  My local linux version is
> > 2.0.7
>
> We've got some code in Storm (https://storm.canonical.com/) that
> handles disconnects in what I consider a reliable fashion.  There are
> two main issues to consider when handling disconnects:
>
> 1. transaction boundaries.  Imagine the following sequence of events:
>  * begin transaction
>  * update table 1
>  * update table 2
>  * commit transaction
>
> If a disconnect occurs between the second and third events and you
> simply reconnect immediately, only the second update will be recorded
> to the database even though the application was trying to perform both
> updates atomically.  The disconnection code in Storm only tries to
> reconnect to the database after you call rollback(), so cases like the
> above won't result in partial updates.
>
> This makes it possible to treat the disconnection the same way you'd
> handle a dead lock.
>
> 2. per-connection variables.  PostgreSQL has a bunch of per-connection
> variables that can control things like statement timeouts, time zones,
> date formats, etc.  If you change any of those variables, those
> changes will be lost when you reconnect.  This really requires the
> involvement of the application to properly handle.
>
> So it might be possible to implement something like (1) in psycopg2, I
> don't know about (2).
>
> James.

At the moment I'm not to sure what I will do with respect to the actual  
disconnect.  As I see it the disconnect will time out on the server side and 
will rollback the transaction anyway - so the lost connection is not a data 
corruption problem.  The question in my mind is what happens after I 
reconnect.  In either case I think I will create a timer to check the 
connection and also check the connection as I start any type of transaction 
on the server.  Thanks for your thoughts.


-- 
John Fabiani