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

PostgresSQL 9.1 and 9.2 are out since a while and - among a long list of changes - one noticable change for OpenACS is that standard conforming strings are now the default [1] (instead of the non-standard backslash escaping used by openacs). This change effects nspostgres as well as many of the .sql files in oacs-core, which are written in the hard-to-read single-quote style with backslash escapes, and not in the recommended $$-quoting style of PL/pgSQL (see e.g. [2]).

In july 2011 i have started the migration of the most essential OpenACS postgres .sql files(e.g. acs-kernel), and finished today the reminder of the function definitions of the openacs-core, acs-events, categories, file-storage, rss-support and general-comments (changes in more than 120 files).

In essence, the changes are:
- upgrade to the $$ notation (recommended since pg8.0, jan 2005).
- get rid of backslash usages in function definitons
- drop aliases in favor of named function arguments (recommended since pg8.0)
- fix wrong function_args, add missing function_args, align default semantics
with the defaults in pg (providing "null" as default means the argument is

The changed files require at least PostgreSQL 8.0, which predates the oldest community supported version 8.4 significantly [3].

I have tested the changes with the regression test of acs-core and have installed various supporting packages and xowiki with the changed definitions. I can certainly not exclude that some bugs sneaked in, but i hope these are not too many. The changes are committed to HEAD of the openacs repository.

best regards
-gustaf neumann


The $$ notation for quotes are great, it will make the '''''' style of quoting go away forever, no one likes to be as confused as that can cause. You can still use ' quotes, they only need to be used for strings, which is the way it's supposed to be.

Backslash usages, could you name one plpgsql func using that so I can see what you're talking about?

Dropping aliases, I'll just note that the api browser should be adjusted to handle non-aliased parameter listings; as it stands the most useful source view is in the original source files.

You state (providing "null" as default means the argument is
optional), and I would ask, doesn't anything provided as default for a formal parameter mean the corresponding actual parameter is optional? Is it that providing anything other than null would cause the parameter to be supplied whereas if the default is null, that causes the parameter to not be supplied at all?

I've considered situations like this, and (while I recognize this is one of many ways) I always thought that a parameter that defaults to null should just be passed, where the function itself will see the null, recognize it as the default case and respond as appropriate. I had never considered failing to provide the parameter instead. Your thoughts on this part?

All in all, this is all going towards allowing the use of pg beyond 8, it's probably necessary, and some points will make reading and maintaining the plpgsql funcs easier.

Finally, I didn't see a TIP... are you going to TIP this stuff?


PS Gustaf, thank you for your support; this all looks great.
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.*


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

Still some work to be done, you're keeping track of that... great... I've been wondering about pg-9.1 and 9.2 as the 8.x series are getting toward end of life. Thanks for thinking about this and keeping track of what is needed next, etc.

There is a piece of the API browser that displays plpgsql funcs; right now it looks at the metadata from pg and always shows the func's formal parameters as aliased ($1, %2, etc) and I was wondering if there is someone who wants to look at it and/or someone who knows that section of the code... preferably both who want to join as a team to see about displaying the definition header more accurately, which will either require storing its complete text or looking at the metadata for each parameter and displaying it as usefully as possible.

Finally, assume for this question you want to install an openacs that needs pg-9.2. If you build the pg and get it going, what is necessary to configure the backend (in postgresql.conf)? Does it need changing or can it be left the same as the default config file?

Thank you so much to everyone who worked on this! I feel a bit of life has been restored.


i am not sure, where in the api-browser you are exactly looking at, but you should be aware, that (a) not all packages are updated to the dollar-quoted syntax, and that (b) not all of the installed functions are changed in case of upgrading from older installations.

What's still missing are more upgrade scripts to make sure, that all define_function_args on all core packages are on the same level for upgrades and new installations.

Concerning the postgres config files for pg9.2: one can use the stock postgres config files, no modifications are required. This make life easier, when binary distributions are used. One might want to tune it, but this is optional.

best regards
-gustaf neumann

Hi Gustaff,

I've tried to install OpenACS 9.1 on postgresql 9.1 twice and I got the error bellow.

1. May I assume this rewriting work is still in progress?

2. Is OpenACS 5.7.0 able to be installed from scratch on postgreSQL 9.1?

3. May I go through the .sql packages and get syntax errors fixed?

4. Is there anything that I can help?

Please, see error.log

[23/May/2013:19:06:50][18171.18446744073054029568][-default:2-] Notice: 0

[23/May/2013:19:06:50][18171.18446744073054029568][-default:2-] Notice: (1 row)

[23/May/2013:19:06:50][18171.18446744073054029568][-default:2-] Error: Error sourcing /var/www/planetnatopia/packages/acs-bootstrap-installer/installer/install.tcl:
psql:postgresql.sql:44: ERROR: syntax error at or near "\"
LINE 17: v_inc := \-1;
str alias for $1;
pat alias for $2;
dir alias for $3;
cnt alias for $4;
v_len integer;
v_i integer;
v_c char;
v_cnt integer;
v_inc integer;
v_len := length(str);
v_cnt := 0;

if dir < 0 then
v_inc := \-1;
v_i := v_len;
v_inc := 1;
v_i := 1;
end if;

while v_i > 0 and v_i <= v_len LOOP
v_c := substr(str,v_i,1);
if v_c::char = pat::char then
v_cnt := v_cnt + 1;
if v_cnt = cnt then
return v_i;
end if;
end if;
v_i := v_i + v_inc;
end loop;

return 0;

psql:postgresql.sql:142: ERROR: syntax error at or near "\"
LINE 27: v_one_arg := substr(v_args, 1, v_pos \- 1);
fname alias for $1;
nargs integer default 0;
v_pos integer;
v_funcdef text;
v_args varchar;
v_one_arg varchar;
v_one_type varchar;
v_nargs integer;
v_funcdef := 'drop function ' || fname || '(';

select proargtypes, pronargs
into v_args, v_nargs
from pg_proc
where proname = fname::name;

v_pos := position(' ' in v_args);

while nargs < v_nargs loop
nargs := nargs + 1;
if nargs = v_nargs then
v_one_arg := v_args;
v_args := '';
v_one_arg := substr(v_args, 1, v_pos \- 1);
v_args := substr(v_args, v_pos + 1);
v_pos := position(' ' in v_args);
end if;
select case when nargs = 1
then typname
else ',' || typname
end into v_one_type
from pg_type
where oid = v_one_arg::integer;
v_funcdef := v_funcdef || v_one_type;
end loop;
v_funcdef := v_funcdef || ') CASCADE';

return v_funcdef;

psql:postgresql.sql:247: invalid command \
psql:postgresql.sql:249: ERROR: syntax error at or near "return"
LINE 46: return v_funcdef;
psql:postgresql.sql:251: WARNING: there is no transaction in progress
psql:postgresql.sql:255: ERROR: syntax error at or near "' language '"
LINE 1: ' language 'plpgsql' stable strict;
psql:postgresql.sql:256: ERROR: syntax error at or near "args"
LINE 1: args alias for $2;
psql:postgresql.sql:257: ERROR: syntax error at or near "v_src"
LINE 1: v_src text;
psql:postgresql.sql:258: ERROR: syntax error at or near "pos"
LINE 1: pos integer;

Have you updated your nspostgres driver to use the SQL escape string syntax E'...' when needed (required in PostgreSql 9.1)?

Yes, however it didn't work out.

I installed aolserver4-nspostgres via apt-get. Thus, the version available isn't up to date, meaning it isn't postgresql9.1 compliant.

Then, I tried to update by downloading the tarball available on SF (the link you provided) and I got the error bellow at compilation step.

But I got an error regarding a missing file: libpq-fe.h.

In file included from nspostgres.c:32:0:nspostgres.h:48:22: fatal error: libpq-fe.h: No such file or directory
compilation terminated.
make: *** [nspostgres.o] Error 1

Then I installed lipq-dev. (apt-get install libpq-dev)

And tried to compile nspostgres again. It was unsuccessful but the error changed to:

root@hermes:/usr/local/src/nspostgres# export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/postgresql/9.1/lib/
root@hermes:/usr/local/src/nspostgres# make install POSTGRES=LSB ACS=1 INST=/usr/lib/aolserver4 AOLSERVER=/usr/lib/aolserver4
/usr/lib/aolserver4 -d /usr/lib/aolserver4/bin -e
make: execvp: /usr/lib/aolserver4: Permission denied
make: *** [install-mod] Error 127

Then, I found a thread, pasted bellow, but that didn't solve the problem.

The thread basically tells to amend the line within Makefile.module

MODLIBS = -L$(PGLIB) -lpq -lnsdb

With that, compiling process generated and

But it seems those aren't pg9.1 compliant since I still get syntax error at OpenACS core installation

What else should I do?

Are you sure, you used the updated version? The Makefile in the actual version of nspostgres contains already -lnsdb (see As it looks from your posting, you added -lnsdb yourself. To be sure, the ChangeLog should contain a message concerning nspostgres_v4_r2.

Before compiling, it is a good advice to read the README file. It mentions hints, how to build the driver on various configurations. None of these mentions POSTGRES=LSB.

With the updated version, you should be able to build nspostgres with a make command like the following (adjust the paths to your needs)

make ACS=1 POSTGRES=/usr/local/pg923/ AOLSERVER=/usr/local/aolserver-4.5/

See also Jim's posting for more options

When you get a message during "make .... install" about "Permission denied", it means that the current user has not sufficient priviledges. You will have typically are "make install" to a system-wide directory with root permissions.

Hope this helps
-gustaf neumann


I used the link you provided earlier.
I opened Changelog and ratified that I had the release you mentioned.

2011-07-09 Gustaf Neumann
tag nspostgres_v4_r2

I see no reason to the message "Permission denied" since I executed make and everything else as root user

I ran a rollback to a previous stage and started AOLServer installation again, untill the point of upgrading nspostgres with the tarball provided v4_r2.

I unpacked it and ran "make". That worked fine

root@hermes:/usr/local/src/nspostgres# make POSTGRES=/usr/lib/postgresql/9.1 ACS=1 INST=/usr/lib/aolserver4 AOLSERVER=/usr/lib/aolserver4
ar rv libnspostgres.a
ar: creating libnspostgres.a
ranlib libnspostgres.a

However after "make" I ran "make install" and that returned again the permission error .

root@hermes:/usr/local/src/nspostgres# make install POSTGRES=/usr/lib/postgresql/9.1 ACS=1 INST=/usr/lib/aolserver4 AOLSERVER=/usr/lib/aolserver4

HAVE_UINTPTR_T=1 -DHAVE_SIGNED_CHAR=1 -DHAVE_LANGINFO=1 -DHAVE_SYS_IOCTL_H=1 -DTCL_UNLOAD_DLLS=1 -DPACKAGE_NAME=\"\" -DPACKAGE_TARNAME=\"\" -DPACKAGE_VERSION=\"\" -DPACKAGE_STRING=\"\" -DPACKAGE_BUGREPORT=\"\" -DTCL_CFG_OPTIMIZED=1 -DTCL_CFG_DEBUG=1 -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DHAVE_INTTYPES_H=1 -DHAVE_TIMEGM=1 -DHAVE_DRAND48=1 -DHAVE_RANDOM=1 -DHAVE_POLL=1 -DHAVE_GETADDRINFO=1 -DHAVE_GETNAMEINFO=1 -nostartfiles -Wl,-rpath,/usr/lib/aolserver4/lib -o nspostgres.o -L/usr/lib/postgresql/9.1/lib -lpq -lnsdb -L. -lnspostgres -L/usr/lib/aolserver4/lib -lnsd -lnsthread -L/usr/lib -ltcl8.5 -ldl -lpthread -lieee -lm -Wl,-Bsymbolic-functions -lgcc_s -Wl,-rpath,/usr/lib/aolserver4/lib -Wl,-rpath,/usr/lib/aolserver4/lib
/usr/lib/aolserver4 -d /usr/lib/aolserver4/bin -e
make: execvp: /usr/lib/aolserver4: Permission denied
make: *** [install-mod] Error 127

Regardless the error message, the .so files were created.
I copied them over /usr/lib/aolserver4/bin and /lib

Then I tried to install OACS again and got the same syntax error.

27/May/2013:19:52:19][26107.18446744072681907968-default:2-] Error: Error sourcing /var/www/planetnatopia/packages/acs-bootstrap-installer/installer/install.tcl:
psql:postgresql.sql:44: ERROR: syntax error at or near "\"
LINE 17: v_inc := \-1;

I believe I will need to install all aolserver 4.5 packages from source.

Since the beginning I installed them using apt-get, with some arrangements from previous .deb packages, as I explained on aolserver mailing list

apt-get install aolserver4-dev aolserver4-core aolserver4-dev aolserver4-daemon aolserver4-doc aolserver4-nsopenssl aolserver4-nspostgres aolserver4-nssha1 aolserver4-nsxml aolserver4-xotcl

Best wishes,

Have your read the large warning about INST in the README file of newer version of nspostgres:

NOTE NOTE NOTE!! As of aolserver-4.5.x, the variable INST is used for
a different purpose than it used to be, so DO NOT set it to the installation dir!!

Probably, when you remove INST from your "install" line, the error will go away.

anyhow. can it be, that you are *not* using the head version of openacs for installation?

Yeah, I wrote that :)

You're right on both statements:

1. I removed INST. make and make install ran fine.

2. The version I downloaded is broken (from OACS main page). I downloaded oacs5.7 from CVS Head then the process installation worked fine.

We need to remove this link from the main page of OACS website ASAP.

Iuri, these are good news.

openacs 5.7 does NOT work with pg 9+ (see e.g.; also mentions explicitly openacs 5.8.

We should bundle a release of openacs 5.8 rather soon now to avoid more confusions.

all the best
-gustaf neumann