ZPsycopgDA and no commits

January 17th, 2013

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.

The Problem

Are your updates and inserts in Postgres not being committed?

Your 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?

The Solution

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

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>

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.


Subversion Versioning

October 18th, 2011

When working with a version control repository, it is often useful to know what version of script we are working on or - in a production environment - which version we are actively using. Subversion provides the facility for incorporating the current version number in a script or source file every time it is committed to the repository. Here's how it can be used:

Adding the Subversion revision to a document

This is pretty easy. We just need to set a subversion property on the document or file and make sure a keyword is included in the document where we want the revision number to appear.
  1. to place a revision number in a document or file, include the text item
    where the revision number should appear. This will be replaced by text like
    $Rev: 444$
    the next time you commit your script (the number will be different of course)
  2. to tell subversion to update this text item when we commit, we need to set a property on the file; we can do this with a subversion command like
    svn propset svn:keywords "Rev" 
This is elaborated on in the subversion manual and we can include other items of information using keywords such as date, author or id (a combination of other keywords). There are a couple of issues you need to be aware of:
  1. using the
    key / value
    string as normal content in your document can cause the string to be interpreted as a variable which may need disguising e.g. see the perl and bash scripts below
  2. subversion properties can be fragile and are largely invisible i.e properties cannot be set using wildcards (only by individual filename) and it is not easy to see which files have which properties set

A simple bash script to set properties on all files in a folder might look like this

KEYWORDS="Rev Id Date Author"
SVNPROPS="svn propset svn:keywords"
for FILE in *
    if [ -a $FILE ]

Having this information automatically updated in your subversion content is very useful for documentation purposes, if nothing else.

Using $Rev$ in our version number scheme

Now that your documents or scripts have a revision number embedded in them, how can we use this information to construct a document or script version number using the revision. For instance we could have a version number as 1.2.321 where 321 is a subversion revision number. Generally we need to manipulate this information as a string. Here are a few examples in different languages. First, in python:

__version__ = '0.1.' + '$Rev: 21 $'[6:-2]

In this case we are just treating the revision string as a simple string and slicing it. This will create a version number like 0.1.21, And in perl:

my $Rev=0; my $VERSION="$Rev: 21 $Rev"; my @Ver=split(" ",$VERSION); $VERSION="1.2.$Ver[1]";

Here we use several checks to fool perl: we initialise a $Rev variable so we can include the key-name in a regular string (i.e. rather than being embedded in a comment somewhere); now we can just split the string into parts and pick out the value we want. Simples! to give us a version of 1.2.21. And in bash:

REV="\$Rev: 21 $" REV=${REV/\$Rev: /} REV=${REV// \$/} VERSION="2.3.$REV" 

Here we use some regex logic to strip a revision string of the characters we do not want, to give us a version of 2.3.21

Sadly I cannot claim any of these tricks as my own, nor can I provide the original author's names - they have been lost in the mists of time.


Document Version Numbering in Latex

October 17th, 2011

How useful would it be to include a document version that can increment itself every-time the document file is updated into a subversion repository. Being able to show a revision number on selected pages of Latex documents must prevent the confusion that arises during project documentation as both project and documentation evolve. It is easy enough for the document author to track revisions (embedded in comments) but being able to actually print out a dynamic revision is much more difficult.

Example document version numbering

Example of a document with version number

The idea for this came from an article by Lex Spoon who provided the basic Latex technique for providing a variable that could be rendered and a perl script. Here I will outline a similar solution using python, extending the concept somewhat.
  1. provide major & minor version numbers in a file
  2. use a subversion tool to determine the last commit revision to the current folder or tree
  3. combine the version and revision numbers to make a definitive version reference
  4. save this (and any other information you want) to a TeX file as \newcommand\svnversion{1.2.345}
  5. include this file in the preamble of your main LaTeX document
  6. you can then reference the version anywhere you wish with the variable \svnversion
Lex Spoon provides a perl script which automates this for you, possibly integrating with your build process; my python script (which uses pysvn) has more mundane ambitions and is available on the AFC Commercial OSS download site. To get this working:
  1. download the script from the site and copy it to somewhere in your path e.g. /usr/local/bin
  2. make sure you have the pysvn module installed; download form pysvn.tigris.org if not
  3. go to where your LaTeX document is stored; it should be in part of a subversion tree
  4. create the file sversion.txt to contain the major minor number of your project e.g. 1.2
  5. now run the script sversion (or whatever you called it in step 1); this should create a local file sversion.tex
  6. in the preamble of your LaTeX document add a line like \include{sversion}
  7. you should now be able to use the variable names like \svnrevision and \svnversion to render appropriate numbers
  8. simply run sversion at any-time to update the version numbers
You could even move the files into a more global location for the project and run sversion in that folder.
© 2013 Andy Ferguson