I discovered the solution to this problem a couple of months ago. Goodness knows how I happened upon it but today the same thing occurred. Of course, I had forgotten about the solution, fretting for several hours wondering what was wrong with my code. Best if I write it down this time, eh.
Are your updates and inserts in Postgres not being committed?
You are using a modern Postgres database (8.4 in my case) using ZSQL Methods from Zope or Plone to target the database through ZPsycopgDA and psycopg2. Everything seems to be fine; all your complex reads and queries work great. But when you try to update the database using either update or insert commands, nothing seems to happen. Perhaps you have even been able to verify that the update or insert commands are actually getting through OK.
It just seems as if nothing is getting committed. What is going on?
And yes indeed, this is the problem - your transactions are not being committed.
The reason for this is to do with isolation levels provided by psycopg2. In the adaptor (ZPsycopgDA) not all levels were supported and those that were defined were mapped incorrectly. A double whammy.
The problem should have been resolved in psycopg by now (according to this thread from August 2011) but if you still have a broken version of ZPsycopgDA as included in psycopg 2.4.2, you can make the correction by editing the dtml files add.dtml and edit.dtml in the database adaptor. The selector for the Transaction Isolation Level needs modified.
In old, broken versions, it looked like this:
<select name="tilevel:int">
<option value="1">Read committed</option>
<option value="2" selected="YES">Serializable</option>
</select>Instead, the selector should read something like this
<select name="tilevel:int">
<option value="0" <dtml-if expr="tilevel==0">selected="YES"</dtml-if>>Autocommit</option>
<option value="1" <dtml-if expr="tilevel==1">selected="YES"</dtml-if>>Read Uncommitted</option>
<option value="2" <dtml-if expr="tilevel==2">selected="YES"</dtml-if>>Read Committed</option>
<option value="3" <dtml-if expr="tilevel==3">selected="YES"</dtml-if>>Repeatable Read</option>
<option value="4" <dtml-if expr="tilevel==4">selected="YES"</dtml-if>>Serializable</option>
</select>Once you have restarted your Zope instance you can then access the ZMI and edit the database connector. Choosing “Read Committed” now will set the correct value and your transactions will be committed.
Good news! As of psycopg2 v2.4.6, the distributed ZPsycopgDA has been amended and will now work out-of-the-box and this fix is no longer required. And there is more: the Zope product, ZPsycopgDA, is now available in its own package from github and as an egg on pypi; the availability on pypi should mean it can be included as a requirement in a zope buildout.conf.