Forum OpenACS Development: OpenACS 5.8.1 upgrade for PostgreSQL 9.4.0

I am trying to upgrade to 5.8.1 after migrating my 5.7 install to postgresql 9.4.0.

I have compiled AOLserver 4.5 and nspostgres from CVS. I am getting this error.

Installing Kernel 5.8.1

Installing data model for Kernel 5.8.1...

Loading data model /var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d2-5.8.0d3.sql...

COMMIT
COMMIT
COMMIT
COMMIT

Failed to install Kernel, version 5.8.1. The following error was generated:

psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d2-5.8.0d3.sql:16: ERROR: unterminated dollar-quoted string at or near "$$ DECLARE BEGIN insert into acs_logs (log_id, log_level, log_key, message) values

Loading the upgrade files in PSQL sems to work. Why is the dollar quoting causing a problem when called from within AOLServer?

My install should be using psql from /usr/local/postgresql-9.4.0/bin/ that is set as the path.

Collapse
Posted by Gustaf Neumann on
OpenACS used/uses a non-standard backslash quoting, which requires in pg 9+ a different syntax. Can it be that you are using an older postgres driver for aolserver?

I've contributed several years ago an updated version of nspostgres for aolserver to the cvs repository. Using NaviServer is probably less work for you, since it comes with working drivers, provides you with a better ssl support and has less security flaws and leads to better performance.

For pg9 migration, please check out https://openacs.org/xowiki/upgrade-oacs-5-8

Collapse
Posted by Dave Bauer on
Gustaf, Thanks for the advice.

What I did was to upgrade the database to 9.4.0 then try to run the 5.8.1 upgrade scripts.

This does not work, even after using Naviserver. I still get the dollar quoting error message. I used install-ns to install naviserver.

Installing data model for Kernel 5.8.1...

Loading data model /var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d2-5.8.0d3.sql...

COMMIT
COMMIT
COMMIT
COMMIT

Failed to install Kernel, version 5.8.1. The following error was generated:

psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d2-5.8.0d3.sql:16: ERROR: unterminated dollar-quoted string at or near "$$ DECLARE BEGIN insert into acs_logs (log_id, log_level, log_key, message) values (nextval('t_acs_log_id_seq'), 'notice', notice__log_key, notice__message);" at character 121 psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d2-5.8.0d3.sql:18: ERROR: syntax error at or near "return" at character 5 psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d2-5.8.0d3.sql:19: WARNING: there is no transaction in progress psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d2-5.8.0d3.sql:20: ERROR: unterminated dollar-quoted string at or near "$$ LANGUAGE plpgsql;" at character 1 psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d2-5.8.0d3.sql:35: ERROR: unterminated dollar-quoted string at or near "$$ DECLARE BEGIN insert into acs_logs (log_id, log_level, log_key, message) values

Collapse
Posted by Dave Bauer on
My mistake, 5.7 only can run under 8.4 so the upgrade has to be run under postgresql 8.4. Interesting that the upgrade script format is not compatible with PostgreSQL 9 but as long as the procedure works, its OK!
Collapse
Posted by Dave Bauer on
Bad news!

Running OpenACS 5.7.0 on Naviserver and PostgreSQL 8.4.4 the upgrade scripts still throw the errors.

COMMIT

Failed to install Kernel, version 5.8.1. The following error was generated:

psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d1-5.8.0d2.sql:10: ERROR: unterminated dollar-quoted string at or near "$$ DECLARE v_rec record;" at character 87 psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d1-5.8.0d2.sql:11: ERROR: syntax error at or near "v_drop_cmd" at character 8 psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d1-5.8.0d2.sql:12: ERROR: syntax error at or near "v_pkg_name" at character 8 psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d1-5.8.0d2.sql:14: ERROR: syntax error at or near "raise" at character 15 psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d1-5.8.0d2.sql:15: ERROR: syntax error at or near "v_pkg_name" at character 9 psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d1-5.8.0d2.sql:22: ERROR: syntax error at or near "for" at character 9 psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d1-5.8.0d2.sql:23: ERROR: syntax error at or near "v_drop_cmd" at character 13 psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d1-5.8.0d2.sql:24: ERROR: prepared statement "v_drop_cmd" does not exist psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d1-5.8.0d2.sql:25: ERROR: syntax error at or near "loop" at character 13 psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d1-5.8.0d2.sql:28: ERROR: syntax error at or near "if" at character 9 psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d1-5.8.0d2.sql:30: ERROR: syntax error at or near "else" at character 9 psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d1-5.8.0d2.sql:31: ERROR: syntax error at or near "if" at character 13 psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d1-5.8.0d2.sql:33: ERROR: syntax error at or near "return" at character 9 psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d1-5.8.0d2.sql:35: WARNING: there is no transaction in progress psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d1-5.8.0d2.sql:36: ERROR: unterminated dollar-quoted string at or near "$$ LANGUAGE plpgsql;" at character 1 psql:/var/lib/aolserver/taxonomy/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d1-5.8.0d2.sql:48: ERROR: unterminated dollar-quoted string at or near "$$ DECLARE nargs integer default 0;" at character 103

Collapse
Posted by Gustaf Neumann on
Very strange. The upgrade scripts are loaded via the configured version of psql.

Can it be that the openacs-config file of that installation has a very old or broken pgbin configured? See in section "ns/db/driver/postgres" parameter "pgbin".

Collapse
Posted by Dave Bauer on
Thanks Gustaf! I should have read the sample config file more closely. I didn't realize the pgbin parameter existed. I was just lucky before.

I am not sure why setting the PATH in the nsd-postgres startup script did not allow Naviserver to use the correct psql but setting the parameter is more reliable, I think.

Collapse
Posted by Gustaf Neumann on
The configuration parameter pgbin is a pretty long time in OpenACS: it was added between the releases of oacs-4-6 and oacs-5 in 2003 by Andrew Piskorski. I should have read your first posting more carefully, which contained already all necessary hints.