Forum OpenACS Development: Re: OpenACS 5.8.1 upgrade for PostgreSQL 9.4.0

2: Re: OpenACS 5.8.1 upgrade for PostgreSQL 9.4.0 (response to 1)

Posted by Gustaf Neumann on 01/18/15 10:34 AM

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

3: Re: OpenACS 5.8.1 upgrade for PostgreSQL 9.4.0 (response to 2)

Posted by Dave Bauer on 01/18/15 10:35 PM

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

4: Re: OpenACS 5.8.1 upgrade for PostgreSQL 9.4.0 (response to 3)

Posted by Dave Bauer on 01/18/15 10:44 PM

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!

5: Re: OpenACS 5.8.1 upgrade for PostgreSQL 9.4.0 (response to 4)

Posted by Dave Bauer on 01/18/15 10:48 PM

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

6: Re: OpenACS 5.8.1 upgrade for PostgreSQL 9.4.0 (response to 5)

Posted by Gustaf Neumann on 01/19/15 09:00 AM

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".

7: Re: OpenACS 5.8.1 upgrade for PostgreSQL 9.4.0 (response to 6)

Posted by Dave Bauer on 01/20/15 01:34 AM

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.

8: Re: OpenACS 5.8.1 upgrade for PostgreSQL 9.4.0 (response to 7)

Posted by Gustaf Neumann on 01/20/15 02:20 PM

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.