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

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?

-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

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

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

[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;
^
QUERY:
declare
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;
begin
v_len := length(str);
v_cnt := 0;

if dir < 0 then
v_inc := \-1;
v_i := v_len;
else
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;

end;
psql:postgresql.sql:142: ERROR: syntax error at or near "\"
LINE 27: v_one_arg := substr(v_args, 1, v_pos \- 1);
^
QUERY:
declare
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;
begin
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 := '';
else
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;

end;
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;

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 nspostgres.so
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.

https://openacs.org/forums/message-view?message_id=118559

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

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

With that, compiling process generated nspostgres.so and libnspostgres.so

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

What else should I do?

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

2011-07-09 Gustaf Neumann mailto:neumann@wu-wien.ac.at
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
root@hermes:/usr/local/src/nspostgres#

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.so 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 nspostgres.so
make: execvp: /usr/lib/aolserver4: Permission denied
make: *** [install-mod] Error 127
root@hermes:/usr/local/src/nspostgres#

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;
^
QUERY:

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

i.e.
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,

openacs 5.7 does NOT work with pg 9+ (see e.g. http://www.openacs.org/xowiki/openacs-compatibility-matrix); also http://www.openacs.org/xowiki/Postgres_9 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