Fwd: [Zope] Using psycopg connections directly

[Federico Di Gregorio]

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