The OIO Project has been using the venerable ZPyGreSQLDA to connect from Zope to the PostgreSQL database in the backend since the beginning (mid-1999). PyGreSQL has been entirely stable (although initially Zope was not). However, PyGreSQL is limited to only allowing one user per database adaptor to access the database at any given time. This is a serious limitation that must be overcome for any Zope-based system (that use PostgreSQL) to serve more than 10-20 concurrent users.

This is why Federico Di Gregorio's timely Psycopg project is so critical to the future of the OIO system and others. Psycopg makes it possible for multiple users / sessions to access the same PostgreSQL database and adaptor without blocking! For those of us who have been waiting for this over the past two years, it is truly amazing to see it in action. :-)

As I began the migration of OIO code from ZPyGreSQL to Psycopg, I became aware of differences in data returned by the two. These differences caused exceptions and run-time errors and require modification of logic in the Zope-based software. That's why I decided to write this document so that there will be a place to organize and make available what I have learned. Hopefully, this will make the transition easier for someone else. Of course, if you discover additional differences that I did not list, please email me at andrew_p_ho@eudoramail.com. I will continue to update this how-to as additional differences are discovered.

1. Select max(a) as b from c (if c is an empty table), PyGreSQL returns b=0. Psycopg returns b=None. This is problematic if your program expects an integer. The same issues also applies to min(a).
   • The preferred way to fix this is to use select COALESCE(max(a),0) as b from c. This returns 0 when max(a) is null.
   • Another way to fix this is to use "b is None" or "b == None" (or the uglier "_.str(b)=='None'") to catch this condition in Zope. [thanks to Erno Kuusela for the prettier approaches!]
2. Maximum rows to retrieve
   • ZPygreSQL does not respect the "Maximum rows to retrieve" of the ZSQL Method in Zope while ZPsycopg does. I discovered this when operations that involve ZSQL methods were being truncated to the first 1000 rows after switching over to Psycopg.
   • The fix is to edit each ZSQL method through its "Advanced" properties tab via the Zope management interface. The "Maximum rows to retrieve" needs to be increased accordingly.
   • An alternative way to set Max rows globally is to edit Shared.ZRDB.DA.DA (for Debian: /usr/lib/zope/lib/python/Shared/DC/ZRDB/DA.py) and change its 'max_rows_' default value. [contributed by Alfons Grabher]
3. Exception code/messages are different. PyGreSQL throws pg.error. Psycopg does not.
   • The way to fix this is to either use the all-encompassing except without parameter - or catch the specific error code that Psycopg generates, which are subclasses of psycopg.Error. Details can be found in the PYTHON DB-API specification.

1. Select a,b from c (if b is null). PyGreSQL returns b=null which renders as the empty string. Psycopg returns b=None which renders as the string 'None'.

1. You could also say that the way psycopg returns things is correct while PyGreSQL's is not, since PyGreSQL is not fully DBAPI-2.0 compliant while Psycopg is and Zope requires DBAPI compliance to work best.
2. However, actually the pg module from pygresql doesn't attempt to be even vaguely db-api compliant. it was probably written before db-api existed. So, another way to look at it is that it is not incorrect, just different interface. [Erno Kuusela provided this historical perspective.]
3. Psycopg is the only postgresql driver that manages zope transactions correctly, while many others work in autocommit mode, which is WRONG!! This means that any error happening during a single zope transaction causes the postgresql transaction to be rolled back. For instance it can be very usefull when zope submits data that the user has inserted in an complex html form to the db. Drivers that do not manage transactions could insert wrong or inclomplete data in the db which would quite hard to delete or would require a lot of more logic in the dtml code. [I received feedback that the AUTOCOMMIT _feature_is not "broken" - but maybe it is still wrong :-).]
4. There is a module called pgdb in Pygresql nowadays that attempts to implement db-api (although it is not quite as good as psycopg). Anyways, ZPyGreSQLDA uses "pg", not "pgdb". [contributed by Erno Kuusela]