[Zope-dev] Zope transactions and DAs

[Ross J. Reedstrom]

Hey Zopeists - 
I spent some time this weekend testing the ZPyGreSQLDA with Zope2 beta4.
I seem to have it working reasonably well now, but there are two issues I
want to take up with those with more experience on the guts of the new
TM machinery than me (I guess that means DC folks, primarily).  

First, observed behavior: 
My test system: 
Linux 2.2.10
Zope 2.0.0b4 (Linux x86 binary release: i.e. with it's own python)
Python 1.5.2
PostgreSQL 6.4.2 and 6.5.0

My tests consist of creating a ZPyGreSQL connection, and two Z SQL methods,
one valid (it's: SELECT * FROM pg_tables) and one invalid (SELECT * FROM
no_such_table). Then, with the backend db running with a moderate level of
logging, I use the manage_test interfaces on the Z SQL objects, and follow
what's going on in the PostgreSQL logs.

First, on startup, Zope2 opens 2 connections to the backend DB. This
seems to relate to the number of active threads, since in later tests,
I got more and more connections being created. This is fine, except
only one connection is killed when you close the Zope DB Connection
object. The rest hang around until the Zope process exits. Is there
something I'm supposed to do in the DA to manage the connection pool?

Second, I'm not happy with how the TM machinery is interacting with
my DA.  At first, with a fresh connection, when I use a ZSQL method,
what I see is that Zope calls the _begin(), query(), and _finish()
methods in my DA. However, as soon as there's been an error on a given
db connection (like calling a ZSQL method with syntax errors), Zope only
calls query(). I handle the rollback of the backend inside the query
method: is that correct? Is there some what to get Zope to keep using
the _begin and _finish calls?

Lastly, I'm seeing some hanging in the Zope process, which I'd normally
attribute to me not knowing what I'm doing in my DA code, but may relate
to the lockups others have been reporting. What I see is occasionally,
when I hit the 'Submit Query' button in the manage_test interface,
the HTTP request get sent, then nothing happens. If I hit it again,
I get an immediate response. If I do this enough times, quickly enough,
I sometimes get a new connection to the DB backend, which hangs around
until Zope quits.

Well, that's about it for now. If anyone wants to tryout my code, it's at

http://wallace.ece.rice.edu/ZPyGreSQLDA-0.3-rjr2.tar.gz

DC folk, you can also find it in the obvious place, where the ZDP people
would look.

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005