Sorry, for the ugly post, corrected below:

define XACT_READ_UNCOMMITTED 0
define XACT_READ_COMMITTED 1
define XACT_REPEATABLE_READ 2
define XACT_SERIALIZABLE 3

And in psycopg, file extensions.py we have:

Isolation level values

• ISOLATION_LEVEL_AUTOCOMMIT = 0
• ISOLATION_LEVEL_READ_UNCOMMITTED = 4
• ISOLATION_LEVEL_READ_COMMITTED = 1
• ISOLATION_LEVEL_REPEATABLE_READ = 2
• ISOLATION_LEVEL_SERIALIZABLE = 3

Note that the psycopg constant values are unrelated to the ones in access/xact.h.

Could you please download the zip file from https://github.com/dvarrazzo/psycopg/tree/maint_2_4 and check if everything works as expected? It would be awesome. Thank you.

I have downloaded and installed your ZIP file (both psycopg and ZPsycopgDA)

Comparing previous ZPsycopgDA with the one provided in version 2.4.5, I've noticed:

• few changes in db.py included getconn(False) in getcursor
• edit.dtml and add.dtml using all isolation levels constants
• only version change in DA.py
• huge change in pool.py

I've focused my tests checking that:

• This defect still solved
• That 2 distinct HTTP request do not use the same transaction (checking that pool and getcursor does not return same transaction for 2 distinct http queries)

I've first been disturbed by the fact that my queries where not stored nor executed, but looking at logs, this was due to exception raised because of version number mismatch:
psycopg version mismatch (imported 2.4.6.dev0 (dt dec pq3 ext))

So, after having commented this exception in sources, it was ok for the first point:
In a given transaction using the read-committed isolation level, the second ZSQL executed in a same HTTP request can see changes inserted or updated by the first one.

For the second point it was more difficult to test:
Trying to generate concurrent access is difficult, and identifying which cursor is used in a ZSQL seems difficult because I've not seen any "cursor id" in its interface.
Maybe looking at cursor variable memory address will have been a good choice.

I have followed this path for my test as I'm not very used with Zope DA pools and I had not enough time tonight to investigate your new pool.py:

In db.py, I have updated the query method:

def query(self, query_string, max_rows=None, query_data=None):
    self._register()
    self.calls = self.calls+1

    desc = ()
    res = []
    nselects = 0

    c = self.getcursor()

    try:
        **the_queries = [x for x in query_string.split('\0') if x]**
        for qs in **the_queries**:

            logger.info('Executing Query: %s' % qs)
            try:
                if query_data:
                    c.execute(qs, query_data)
                else:
                    c.execute(qs)
                **if len(the_queries) == 2:
                    import pdb
                    pdb.set_trace()**
            except TransactionRollbackError:

Thus - if you remember my first query, it contains 2 SQL statements: one for the insert, the other to get back last serial number for id_order - when executing the HTTP query doing the insert, it will stop waiting in pdb just after the select curval.

Then, I will go to the ZMI and in the test form for my ZPsycopgDA connector, executing this query select max(id_order) from shop_user_order
If it returns the same value than previous select curval then, cursor/transaction is shared between two HTTP requests and this is terrific, else this is perfect

It appears that second HTTP request does not return last created id_order, so transaction isolation is OK.

So, I confirm that this new release is working for the 2 points tested above.

Hope it will help.

With kind regards.

Oh... By the way, and this may be what you were willing me to do when asking me to test your last ZIP file, I could create Zope Unit test for your ZPsycopgDA as it appears to not own some. (I've closed my IDE, so don't have the sources in front of me to check this point.)

I may be able to implement some next wednesday.

With kind regards.

Testing the specific issue you had reported was what I was expecting, but if you are able to put together some form of repeatable test for Zope, and maybe instruct us layman about how to set up Zope for regular testing, I'd be happy to integrate them into the psycopg test suite.

Thank you in advance!

I still have a remark.

With current implementation, DB.getconn(True) is only called when clicking on the open connection button in ZMI.
It is not called when instances are restarted and DB object created for each instance thread.

So it may be usefull to call self.getconn(True) in DB.__init__

Sorry for having missed this

thank you, I'll take a look into this, maybe Federico knows even better than me.

Looking at the code, I can't understand. I though currently there was a getconn(False) call in DB.__init__ but there is none instead. Except for fixing obvious bugs, I'm against "improving" the adapter in any way: it is largely in maintenance mode.

Maybe the open() method is there for this reason?

If you provide a patch and Federico says it's fine we can apply it, I'm not able to validate it given my missing Zope competence. It would be great to have a test case to reproduce what you say.

MordicusEtCubitus, I've just created https://github.com/psycopg/ZPsycopgDA project, a stand-alone ZPsycopgDA package. If you want to improve the ZPsycopgDA package (including tests, adding features etc) that could be the right place.

Hi,

I've written an email there is a few days to Frederico about ZPsycoPGDA and pool.py to give him a first and short feedback.
I'm actually busy on ZcxOracleDA to finish implementing changes to use bind variables (it works great and may be usefull for ZPsycopgDA too) and merging the pool.py in it (it=ZcxOracleDA).

So I will return you a complete analyze of pool.py and management of Database Adapters by Zope in the way I have understood it.
A few changes may be required in actual code.

I am just too busy today, but tomorrow should be a good moment to send you my comments.

Thanks for ZPsycopgDA github link.
Sure I will use it soon.