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

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