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

4: Re: Rewrite of the .sql files of acs-core and some commonly used packages (response to 1)

Posted by Gustaf Neumann on 04/03/13 12:04 PM

hi jim,

concerning backslash escapes: read section E.9.2.1 in reference [1] above. The backslash (as in e.g. "...\n...") is in the SQL standard an ordinary character, postgres moves this way. The backslashes in SQL strings are introduced in part in the OpenACS source code, and in part from the postgres drivers (which i have updated for aolserver and naviserver as well one or two years ago). One can get the old escape behavior also in new postgres versions via turning standard conforming strings off, but this is not recommended (and might be a problem, when the same database is used for multiple applications, ... or when using packaged database installations, etc).

Concerning defaults: the PL/pgSQL functions of OpenACS did not and still do not use default values, but there are many documentational defaults (saying in a comment that the value defaults to whatever) in the code. Since the the postgres functions were implemented before real defaults were available, the code uses many function with different signatures handling the "defaults" for the missing arguments programmatically. See e.g. the various definitions of content_item__new:

-- procedure content_item__new/20
-- procedure content_item__new/19
-- procedure content_item__new/17
-- procedure content_item__new/17
-- procedure content_item__new/16
-- procedure content_item__new/16
-- procedure content_item__new/16
-- procedure content_item__new/15
-- procedure content_item__new/6
-- procedure content_item__new/5
-- procedure content_item__new/3
-- procedure content_item__new/2

The new function definitions with the named function arguments are nothing more but a requisite to handle this case easier in the future with PL/pgSQL defaults. Using the function argument defaults of PL/pgSQL are supported since pg 8.4, feb 2008. Requiring functions argument defaults now is probably still to early, since once these are used, people are forced to new versions. For several large installations, moving from postgres 8.3 to 8.4 was no good option (see [a]). Since yesterday, we have the victors changes for using recursive queries for permissions in openacs head, so also performance-wise we can now recommend to upgrade to recent PostgreSQL versions.

The comment about "null" was for OpenACS's own way of providing default values via define_function_args.

The rework of the SQL files of OpenACS do not provide any functional changes are they are pure boring maintenance work. But these changes are actually essential for OpenACS. While one can dump/restore old OpenACS databases from earlier versions to e.g. 9.2, one cannot source upgrade scripts or load packages without these modifications.

Hope, this makes the picture clearer.

-gustaf neumann
PS: Btw, i have not touched the upgrade scripts of acs-kernel and acs-content-repository. So it is recommended to run the upgrade scripts in an old postgres installation before switching to 9.*

[a] https://openacs.org/forums/message-view?message_id=3814195

5: Re: Rewrite of the .sql files of acs-core and some commonly used packages (response to 4)

Posted by Michael Steigman on 04/10/13 01:12 AM

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.

7: Re: Rewrite of the .sql files of acs-core and some commonly used packages (response to 5)

Posted by Gustaf Neumann on 04/10/13 09:18 AM

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