[Zope] Using psycopg connections directly

[Cees de Groot]

Hi,

I have an O/R mapping thingy that's written outside of Zope. Because I want to 
make it a good citizen when it is called by e.g. a Zope product, I've extended 
it to use Z Database connections; more specifically, it assumes psycopg 
(metadata intepretation under Python is quite driver dependent, it seems...).

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.

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.

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?


-- 
Cees de Groot               http://www.cdegroot.com     <cg@cdegroot.com>
GnuPG 1024D/E0989E8B 0016 F679 F38D 5946 4ECD  1986 F303 937F E098 9E8B