Lock-ups with multi-threading and psycopg 2.0.11

Hi!

I have a simple multi-threaded web application, and have logs which indicate
that it is locking up inside psycopg about once a week. I'd appreciate
thoughts on if this issue is known or how I can best help debug it if not.


My application is written in Python 2.5.2 using the standard library's
BaseHTTPServer.HTTPServer with the SocketServer.ThreadingMixIn (i.e. each
HTTP request runs in a separate thread). I am running Psycopg 2.0.11 against
PostgreSQL 8.1.4 on Linux.

The database access is simple - I create a single connection centrally with:

  import psycopg2 as dbapi
  assert dbapi.apilevel == '2.0', 'Unexpected DB-API version'
  assert dbapi.threadsafety == 2, 'Threads must be able to share connections'
  db = dbapi.connect(database=config.DATABASE_NAME)
  db.set_isolation_level(0)

Whenever a thread needs to do an SQL query, it creates a cursor, does the
query and destroys it. For example:

  cursor = db.cursor()
  cursor.execute('select ...')
  x = cursor.rowcount
  cursor.close()

These are typically single selects or updates against tables with ~100 rows
and ~5 columns.


The section which typically locks up has the following code:

  logging.debug('1')
  cursor = db.cursor()
  logging.debug('2')  
  cursor.execute('select "type", "resource", "usage", "user" from "resources"')
  logging.debug('3')
  rs = cursor.fetchall()
  logging.debug('4')
  cursor.execute('select "type", "resource", "key", "value" from "resource_properties"')
  logging.debug('5')
  ks = cursor.fetchall()
  logging.debug('6')
  cursor.close()
  logging.debug('7')

In the logs, I see '1' and '2', but not '3' or anything later.

Given that the lock-up is isolated to inside the cursor.execute(), it seems
that it must be in psycopg? If that's the case, can you suggest a solution
or how I can debug this further? For example, how (and where?) should I add
trace inside the psycopg code to pinpoint this further?

I suspect that the problem may be having too many simultaneous open cursors.
Is there a way that I can log how many of these there are? Where is the
limit set, and can I raise it?

Thanks in advance,

Richard.