fog@mixadlive.com said:

...

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.

I know (I wrote ODBC drivers for a living, among others a PostgreSQL one :-)). I sorta expected the Z SQL Connection to do the "right thing", but it forwards the TM messages to the connection, not to individual cursors.

zope has to cope with drivers that are *not* thread safe at level 2 (see the Python DBAPI) and the cursor commit is an extension, not implemented by other drivers. so Zope does the right thing and uses 1 connection per thread.

...

mmm... i think that zope instantiate a connection for every thread. in the psycopg case, every zope thread gets one connection and one cursor.

Let's hope so, although I haven't been able to discern the code that does that bit of magic. Is there a document somewhere that describes what Zope does, thread-wise, when handling requests?

Zope documentation is, mmm... i little bit difficult to find. usually i just read the sources and cross my fingers...

Anyway, I plunged into my code and FYI, this is how I'm now dealing with it:

- select statements grab and release a cursor; - update statements grab a cursor, wrap them in a TM subclass and register the result with the transaction manager. The cursor is also registered in a thread map, so that subsequent update statements in the same thread/transaction reuse the cursor. When the Zope TM calls finish()/abort(), the cursor is committed and released (and removed from the thread map). - session access (updating the "last accessed" timestamp on mostly every request) is done through a special global cursor, which does a commit every 10 or so updates keeping disk write load for this non-critical piece of information low.

The end result is acceptably low connection usage and correct transaction scoping.

pretty nice! will this stuff be released in a Zope Product? 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