[Twisted-Python] twisted.enterprise.adbapi and connection pool problem

[snacktime]

On Mon, 14 Feb 2005 21:50:57 +0100, Michal Chruszcz <troll at pld-linux.org> wrote:
> Hello,
> 
> I've noticed a bug(?) or at least misbehaviour in adapi connection pool.
> I've got a daemon program which keeps a few connections to PostgreSQL
> database and when a client connects and sends some commands it queries the
> database and sends back results. Everything works fine, until I restart
> PostgreSQL. All commands fail and the only trace I've got is the response
> from the database:

Attempting reconnects just isn't part of adbapi as far as I can tell. 
I use psycopg with postgresql and here is how I handle this, maybe
someone else has a better way of doing it.  This tests the database
connection every 10 seconds, and attempts to reconnect 3 times if it
gets an error, otherwise it exits.  It's not very complete but that's
because I just threw it together today...

In my server factory:
-----------------------------------

class Somefactory(Factory):
  def __init__(self):

    self.db = 'dbname'
    self.dbhost = 'dbhostname'
    self.reconnect = 1
    self.dbpool = adbapi.ConnectionPool()
    loop1 = task.LoopingCall(self.checkDatabase)
    loop1.start(10.0) # call every 10 seconds


  def _cbdatabaseError(self,error):

      error_msg =  error.getBriefTraceback()
      if re.search('psycopg.OperationalError',error_msg):
        if self.reconnect <= 3:
          self.dbpool = adbapi.ConnectionPool()
          self.reconnect = self.reconnect + 1
        else:
          reactor.stop()
      else:
        reactor.stop()

  def _cbdatabaseAlive(self,msg):
      self.reconnect = 1
      

  def checkDatabase(self):
    d = self.dbpool.runInteraction(self._cbcheckDatabase)
    d.addCallbacks(self._cbdatabaseAlive,self._cbdatabaseError)

  def _cbcheckDatabase(self,cursor):
    sql = "SELECT 1"
    cursor.execute(sql)
    row = cursor.fetchall()
    return row