Scavenging the mail folder uncovered Paolo Comitini's letter:

Because psycopg is advertised as having a per-cursor commits, initially I just did the same thing in my code as a Z SQL Method would do: call the database object for a connection and run query() on it. However, it turned out that when multiple threads did this, one thread's commit would also commit work on another thread. This is probably due to the fact that the psycopg DB class commits on the connection, not on the cursor it used.

if you want to use the per-cursor commit extension of psycopg, you need to call the .commit() method on the *cursor*. calling it on the connection will commit on all the cursors derived from the connection.

So I now grab a cursor, and register that (inside a wrapper) with the transaction manager, for every query. The finish/abort code does a commit/rollback and then a cursor.close() (shortly after that, the GC destroys the cursor). However, I seem to be leaking connections, because the number of in-use connections quickly goes up and Postgres starts complaining. There's probably some interaction between what I do and psycopg that I don't understand.

note that psycopg open one connection for every cursor (that will change in the near future) so, if you don't set a maxconn value on the connection but keep creating cursors, you'll finish with an hight number of phisical db connections (maxconn is about 32 by default.)

Would anyone have a suggestion as to how I could use psycopg Z Database connections from Python code in such a way that everything works smoothly? What's the Zope model anyway - does it instantiate one Z connection per thread or do all the threads use the same connection and should the connection figure it out by itself?

mmm... i think that zope instantiate a connection for every thread. in the psycopg case, every zope thread gets one connection and one cursor. hope this helps, ciao, federico -- Federico Di Gregorio MIXAD LIVE Chief of Research & Technology fog@mixadlive.com Debian GNU/Linux Developer & Italian Press Contact fog@debian.org Qu'est ce que la folie? Juste un sentiment de liberté si fort qu'on en oublie ce qui nous rattache au monde... -- J. de Loctra