Listing Databases in Postgres

January 22nd, 2013

When working with an SQL database, especially in a development environment, it is easy to lose track of which databases are in which instance. MySQL has a really convenient way of listing its databases with the command

SHOW DATABASES;

in a client session or even programmatically. But then, structurally MySQL is a good deal simpler - or at least it was - than Postgres.

 

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

Update

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.

 
Search for Posts
2014
 
2013
 
2012
 
2011
 
2010
 
 
© 2013 Andy Ferguson