This project is archived and is in readonly mode.

#123 ✓resolved
Wolfgang Eibner

ZPsycopgDA2

Reported by Wolfgang Eibner | September 18th, 2012 @ 08:56 AM

Hi,

we've encountered a strange Zope / ZPsycopgDA2 problem in the last days: It happens very rarely (more often if a lot of concurrent users use our Zope application) that Zope doesn't commit (end) a transaction after the page was loaded. This transaction is still idle / held open and used for following selects or actions of the application (by Zope).

This leads to the following problems:

  • Other users dont't see the changes because they are in another transaction and the editing connection was not committed yet.

  • If the editing user reloads the page displaying the data he get's different connections from the psycopg connection pool. Depending on the connection he has the still open transaction or another one. So sometimes after an edit it seems that the edit just not happened or the edited data got lost.

  • So, most of the users try to edit the dataset again, which of course leads to several problems (mostly a deadlock) because of the first transaction which is still holding a row exclusive lock for updating. Afterwards (or after a db restart) all edited data get lost because of the still pending transactions.

I've verified all of this by logging the statements at postgresql.

Facts so far:

  • Zope has a transaction management which commits transactions after a page was loaded successfully. If an error is raised the transaction is rolled back. Our page structure is (simplified) as follows: A form is used to update data (UPDATE statement) and after submitting the form the user is redirected to a page which displays the dataset (a bunch of SELECT statements). So after the last select statement of the page (or after the update statement?) the commit should be made. Most of the time it is, but sometimes not (without any error displayed or logged by Zope).

  • One should not have commits in ZSQLMethods because this breaks Zopes transaction management. Actually we don't have any commit statement in our ZSQLMethods.

  • We use an "old" Zope 2.10.3-final on python 2.4.4 with psycopg2 2.0.6-1ubuntu1 prepackaged on Ubuntu 7.10. Updating the whole environment would be very difficult.

  • I could sometimes replay the error with a simple test page which only calls a ZSQLMethod for updating and another one for selecting the data.

  • I found some mailing list entries stating that this is a problem with psycopg 2.0b6?

  • I tried the newest version of psycopg2 and ZPsycopgDA2 (2.4.5): I get a lot of rollbacks. I think because Zope doesn't commit select statements or Zope only commits at the end of a page load. The connection is meanwhile (between two ZSQLMethods) put back into the pool and at putting it back idle transactions are rolled back (I think ticket #69 introduced this behaviour). So for my little test case: If I just do the update or the select everything works fine and I get a commit. If I do both in a dtml page I the first one of them gets rolled back.

Could you please help me with this very strange situation. Is it Zope or is it psycopg (or a programming error)? We've never had that before in other Zope applications. My only solution at this time would be commit statements after each update, insert or delete in a ZSQLMethod.

Best regards,
Wolfgang

Comments and changes to this ticket

  • Daniele Varrazzo

    Daniele Varrazzo September 18th, 2012 @ 11:13 AM

    Hello Wolfgang,

    The change in the pool you mention was probably ticket #62, introduced in psycopg 2.4.3.

    As you describe it, that change has totally broken psycopg in zope? That's poor, also because it has never been reported in more than one year.

    The way you describe the bug it seems an issue in zope losing track of its state, but I can hardly blame a software I don't know.

    I cannot debug zope, sorry: I don't know anything about that. Please post the description of your issue to the mailing list and check if anybody there can help you better. Thank you.

  • Daniele Varrazzo

    Daniele Varrazzo September 18th, 2012 @ 12:16 PM

    Open ticket #125 to track the issue of the rollback with psycopg 2.4.5. Wolfgang, can you please test the patch attached? Thank you.

  • Wolfgang Eibner

    Wolfgang Eibner September 18th, 2012 @ 01:22 PM

    I'll try but I have to do it on a test machine because my initial error on the production machine is currently not happening any more and I don't want to destabilize this status (unfortunately it will happen again some day).

    I'll give you a feedback today or tomorrow about the patch.

  • Daniele Varrazzo

    Daniele Varrazzo September 20th, 2012 @ 02:45 PM

    I have some other improvements for zope, see ticket #125. But I will need some help to test with a page calling two ZSQLMethods: I don't know how to set it up.

  • Daniele Varrazzo

    Daniele Varrazzo December 3rd, 2012 @ 12:28 AM

    • State changed from “new” to “resolved”

    The issue should be fixed in psycopg 2.4.6

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

Referenced by

Pages