This project is archived and is in readonly mode.

#142 ✓resolved
MordicusEtCubitus

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

Reported by MordicusEtCubitus | December 3rd, 2012 @ 03:45 PM

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-br... [2] http://grokbase.com/t/postgresql/psycopg/11893j8ns1/zpsycopgda-isol...

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."""

      = 0
= 4
  = 1
 = 2
    = 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.

Comments and changes to this ticket

  • MordicusEtCubitus

    MordicusEtCubitus December 3rd, 2012 @ 03:47 PM

    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
  • Daniele Varrazzo

    Daniele Varrazzo December 3rd, 2012 @ 04:46 PM

    • State changed from “new” to “open”

    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.

  • Daniele Varrazzo

    Daniele Varrazzo December 3rd, 2012 @ 04:46 PM

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

  • Daniele Varrazzo

    Daniele Varrazzo December 3rd, 2012 @ 04:56 PM

    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.

  • MordicusEtCubitus

    MordicusEtCubitus December 3rd, 2012 @ 11:11 PM

    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.

  • MordicusEtCubitus

    MordicusEtCubitus December 4th, 2012 @ 12:09 AM

    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.

  • Daniele Varrazzo

    Daniele Varrazzo December 4th, 2012 @ 12:15 AM

    • State changed from “open” to “resolved”

    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.

  • Daniele Varrazzo

    Daniele Varrazzo December 4th, 2012 @ 12:18 AM

    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!

  • MordicusEtCubitus

    MordicusEtCubitus December 10th, 2012 @ 08:29 AM

    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

  • Daniele Varrazzo

    Daniele Varrazzo December 10th, 2012 @ 10:23 AM

    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.

  • Daniele Varrazzo

    Daniele Varrazzo December 11th, 2012 @ 01:36 AM

    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.

  • MordicusEtCubitus

    MordicusEtCubitus December 11th, 2012 @ 10:06 AM

    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.

  • MordicusEtCubitus

    MordicusEtCubitus December 11th, 2012 @ 10:07 AM

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

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

<b>WARNING:</b> the informations in this tracker are archived. Please submit new tickets or comments to <a href="https://github.com/psycopg/psycopg2/issues">the new tracker</a>.
<br/>
Psycopg is the most used PostgreSQL adapter for the Python programming language. At the core it fully implements the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL.

Shared Ticket Bins

Pages