[Zope] SQL transaction uncommited?

[Tino Wildenhain]

Am Samstag, den 17.12.2005, 14:20 +0100 schrieb Vlada Macek:
> [At 17.12.2005 13:49, Andreas Jung kindly sent the following quotation.]
> 
> > Dealing with transaction is the task of the database adapter and not
> > the ZSQL methods. If you need to deal with transactions yourself you
> > must subclass the transaction manager TM and implement the hooks for
> > commit and abort (see TM.py).
> 
> Yes, I looked at TM.py. I should have mentioned that I use ZPsycopgDA
> and ZPsycopgDA/db.py is subclassing and using TM already. So I was
> expecting the _finish() and _abort() are bound to PostgreSQL in the SQL
> connection I use.
> 
> In the meanwhile I also discovered that when I put my 'insert into
> testtable...' to ZSQL method, the behavior is the same -- psql does not
> see new row, but the sequence is incremented. Now it's strange, isn't?

Actually not. sequences are incremented under any circumstances.
Thats per definition.

> With each ZSQL method call I now found out that a new connection to the
> database is keeping opened and RowExclusiveLock for testtable and
> AccessShareLock for its sequence is pending. In now have tens of
> connections and locks from my tryings. I may did some error in
> configuration, but I have no clue where and which.
> 
> Python 2.3.5, Zope 2.7.5-final, psycopg2-2.0b6, PostgreSQL 8.1.

I wonder how you actually call it in context of zope. 
Usually with the first ZSQL method you execute your connection
to the database takes part of zopes transaction machinery.
If all goes well and no exception bubbles up to the
zpublisher, it commits that transaction - both on zope and
the database. In your case something seems to prevent that,
this could be an error from one of your queries which you
swallow in some try:/except: somewhere?

Maybe you can lay out a simple test case where this happens.

Regards
Tino