Forum OpenACS Development: Re: Rewrite of the .sql files of acs-core and some commonly used packages

Huge thanks to you and Victor for these updates, Gustaf. We were in the situation described in the linked thread - tried upgrading to 9.0 in 2011 but could not due to performance. I have been running 9.2 in my dev virtual machine with Victor's permissions package, testing and converting queries to use acs_permissions__permission_p. For posterity (maybe this is documented somewhere?), I found that I had to install the 9.2 extension for ltree into an empty database before I could reload our 8.3 dump. This was required because at some time in our installation's ancient history, ltree was installed and as a result, the xotcl db procs used columns of type ltree in xowiki object tables. Anyway, so far, so good.

I don't understand the recommendation in your PS regarding the kernel and CR, though. Can you elaborate a little bit? We are currently running 8.3 with all package upgrades < 5.8.0* applied. My plan had been to bring in the latest commits from CVS and run them once the database has been reloaded in 9.2 (as I did with the permissions package version of the changes).

Thanks again.

Hi Michael,

Concerning ltree: The situation with pg has not changed in this respect over the last year: if one wants to install a dump with required extensions, one has to care about his. We seldomly use ltree, bit more and more hstore, which has an integration with ::xowiki::FormPages, the same situation. One has to option to drop eg. the ltree index before producing the dump. Rebuilding an index is easy. Btw, ltree and hstore are available via popular distributions such as debian/ubuntu in the postgres-contrib package.

Concerning upgrades: if one is planing to upgrade from pg8 to pg9, one should first upgrade with pg8 to the current OpenACS version (for now, oacs-core in CVS head), then produce a dump load the dump into pg9.

Actually, i am afraid, one has to be more precise on the versions here. When updating from pg 8.3, there is a potential problem with recursive query processing. While the .xql files can differentiate between pg versions, the upgrade scripts do not. So, if one is loading the latests upgrade script for recursive query processing from victor (acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d3-5.8.0d4.sql) into pg8.3 there might be a problem, since recursive queries were introduced in pg8.4.

So, for upgrading from pg8.3 (or earlier), I would recommend to upgrade first the database to pg8.4 (i.e. load the dump from the pg8.3 installation into pg8.4), then run all upgrade scripts, dump again and load the new dump into pg9.2

Running the old upgrade scripts in pg9 might run into problem with quoting and backslashing, but it might depend from which version one is starting. It might be possible to change the syntax in offending old upgrade scripts to be pg9 friendly, but one has to have a pg8.3 installation to figure this out an to test it.

For the kernel and CR, Victor has added last year update scripts to bring the function_args and the necessary functions to the same state as in new installations (see e.g. acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d1-5.8.0d2.sql).

best regards
-gustaf neumann