ZPsycopgDA: Transaction reseted each time a query is executed (Proposed solution included to set it as SOLVED)

[psycoteam]

Originally submitted by: MordicusEtCubitus

Hi,

I've upgraded psycopg to 2.4.5 and associated ZPsycoPGDA on my website and transactions appear to be reseted each time a new ZSQL method is called in same request. Using postgresql 9.1.6.

My understanding of the error is based, in parts, on the following articles:

[1] http://psycopg.lighthouseapp.com/projects/62710/tickets/125-pool-broken-for-zope
[2] http://grokbase.com/t/postgresql/psycopg/11893j8ns1/zpsycopgda-isolation-levels-bug

Description of the issue:

I am executing two queries in same HTTP request, ie two ZSQL methods

Query one:

insert into shop_user_order ( ) values
( )

SELECT currval( 'shop_user_order_id_order_seq' ) AS id_order

This one executes an insert and return the new id_order of this insert

Query two:

insert into shop_user_order_item ( , reference, title, price, price_promo, tva, count, present, code_promo )
values
(

)

Then I've got the error:

insert or update on table "shop_user_order_item" violates foreign key constraint "shop_user_order_item_fkey" DETAIL: Key (id_order)=(9886) is not present in table "shop_user_order".

First, I've thought that each query was executed in a separated thread/connection, so the new serial of the order was not found in second query.

While reading the article in [2] I've found that some isolation levels where missing and existing one having wrong values.
But proposed patch was in [2] was not solving the issue.

Thus, adding a commit statement just after insert in first query or using auto-commit/read uncommited isolation state were solving the issue.

Clearly, queries were not executed in the same transaction.

While reading [1] I've undersood that second query was calling
self.getcursor()
itself calling self.getconn()
itself doing and init (def getconn(self, init=True):) and reseting connection.

So, updating in db.py

def getcursor(self):
    conn = self.getconn()
    return conn.cursor()

To

def getcursor(self):
    conn = self.getconn(False)
    return conn.cursor()

was solving the issue.
The "open" method is already doing self.getconn(True) so previous change is not risky.

About isolation level values, I've also noticed an error:

PostgresSQL sources in file access/xact.h defines below values:

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

So it appears we have a confusion between auto-commit and READ_UNCOMMITTED

I hope this can help.
By the way, if you need testors for next release of ZPsycoPGDA, I'll be glad to help to.

With kind regards.

[psycoteam]

Originally submitted by: MordicusEtCubitus

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

[dvarrazzo]

Oh, finally, there is that bloody bug :) Since 2004...

And I'd also looked for it, I was sure we were calling getconn() without False somewhere. And it's obviously that one, I don't know how I missed it.

[dvarrazzo]

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

[dvarrazzo]

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.

[psycoteam]

Originally submitted by: MordicusEtCubitus

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.

[psycoteam]

Originally submitted by: MordicusEtCubitus

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.

[dvarrazzo]

Thank you very much, that definitely helped. It's the first time I get a proper review of what was going wrong in Zope.

Sorry for not warning you about the version number check, hope it didn't cost you too much time debugging it.

Best regards.

[dvarrazzo]

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!

[psycoteam]

Originally submitted by: MordicusEtCubitus

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

[dvarrazzo]

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.

[dvarrazzo]

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.

[psycoteam]

Originally submitted by: MordicusEtCubitus

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.

[psycoteam]

Originally submitted by: MordicusEtCubitus

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

[psycoteam]

Originally submitted by: ecigarette2012

Dreambox DM800 HD PVR
dm800
800hd
dreambox dm800hd
dm 800 hd
dm 800 hd pvr
dreambox hd
dreambox 800 hd
dm 800
dreambox dm 800 hd pvr
dreambox dm 800 hd
dreambox 800
dm800hd
dreambox dm800
dm800 hd pvr
Dreambox DM800C

Dreambox dm800S
dreambox 800s

Dreambox DM500S

dm500S
dm 500S
dm500
dreambox 500
dreambox 500s
dm500c
Dreambox dm500T
Dreambox DM500T

ORTON HD X403P

Dreambox DM500Si

electronics cigarette

electronic cigarette liquid premium

electronic cigarette electronic

cigarette manufacturer

cheapest electronic cigarette

china electronic cigarette

electronic cigarette reviews

electronic cigarette starter kits

electronic cigarette price