Postgresql (PG) 9.0 was released on the 20th of September (check out the announcement ) and even though there are many cool features that OpenACS could benefit from, there are some points on which you have to be careful when upgrading your databases to this version.

The OpenACS core packages (HEAD version) are already installable on top of Postgresql 9.0 ( check out the commit on HEAD ) this means that Openacs 5.7.0 will support PG 9.0, but what about your local code? are you ready to upgrade? In order to give you some pointers, I will list quickly some changes that happened on PG 9.0 that could affect your local code:

PL/pgSQL lexer and parser have been reworked:

SQL reserved keywords are also reserved keywords in the PL/pgSQL context. This means that you are not allowed anymore to e.g., name a record: inner. If the case is that you are using SQL reserved keywords for what they are not suppose to, then PG will complain about invalid syntax errors within your PL/pgSQL code.

Server setting "ad_missing_from" removed:

This server setting has been removed ( previously defaulted to off ), which means that you will need to modify local queries to which PG automatically adds the FROM clause. Those should be easy to spot, usually the driver connecting from AOLServer/Naviserver to PG will report warnings on the error log indicating to which queries the FROM clause was added, a simple grep on those error logs should help you here.

Formerly, the usage of sequences on OpenACS fitted this case. Next values of sequences were fetched on select or insert clauses from views. So if in your local code you are using also this technic I would recommend:

• On your tcl scripts, use the db_nextval procedure for fetching next values of sequences, this procedure already deals with the fact that some values are retrieved from views that were created on top of sequences.
• When dealing with this case in your PL/pgSQL functions, avoid the usage of views for fetching next values, instead use the sequence manipulation functions that PG provides, e.g.: nextval, currval, etc.

Run-time parameter "regex_flavor" removed:

In case you don't know, this parameter defines the form of regular expressions accepted by PG. It has been removed and previously its default value was "advance". For installations of OpenACS running on Postgresql 8.x users were requested to set this parameter to "extended" in order to preserve maximum backwards compatibility with pre-7.4 releases. Even though I don't think the removal of this parameter affects the core packages, it might be wise to check regular expressions used in your local code.

Server setting "default_with_oids" defaults to off:

In PG 8.0 and earlier versions this parameter was set to on. It simply determines weather creation of tables includes an OID column, which is considered deprecated currently. This setting can be still enabled for compatibility in case your local code depends on OID's. So far, I have not seen core code that depends on setting this parameter back to on on PG 9.0.

You should be on the safe side if you take into account the previous list of points before migrating your installation to PG 9.0.

Among the added features to this release of PG, it is worth metioning that now the procedural language PL/pgSQL is added by default to the template databases. So this plus the fact that the parameters/server settings mentioned before don't need to be set anymore in your PG configuration files, facilitate the process of installing OpenACS on PG per se. One step forward for OpenACS in this matter. Also, you might be interested in the fact there is a new implementation of VACUUM FULL which is faster.