Re: keep a connection alive

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.