ZPsycopgDA2

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 PQescapeByteaConn does not escape backslashes on postgres 9.1 server 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