Forum OpenACS Q&A: 4.6 upgrade questions

Collapse
Posted by Kevin Murphy on
Q: Does OpenACS 4.6 require Postgres 7.2.X?  Maybe my upgrade failure at /acs-admin/apm/packages-install-4 (see details below) relates to this.

Q: If so, hopefully Roberto could mention this in his release notes?

Q: If not ... any suggestions?

Q: Is there a document that describes generic upgrade procedures? (I.e. some part of the install docs?  Didn't see anything ....)  Vinod's installation docs should at least allude to this, I think.

Q: Does 4.6 really require AOLServer 3.3? I seem to be using 3.4.2, and it is working with OACS 4.5, but I may not be using the functionality that would cause breakage.

BTW, thanks very much to all the developers and maintainers for this very cool framework.  My first project is an FCC policy e-advocacy site (actually just a glorified database-backed form processing backend, so far).  Phase 1 has been running for several weeks and has had several hundred users.  I am hoping to transform it into a generic advocacy system.

Thanks for the help,
Kevin Murphy

- - - -

Installing packages...

Installing data model for ACS Content Repository 4.6...
Loading data model /web/med_dev/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-4.5-4.6.sql...
INSERT 478331 1
INSERT 478332 1
INSERT 478333 1
INSERT 478334 1
INSERT 478335 1
INSERT 478336 1
INSERT 478337 1
INSERT 478338 1
INSERT 478339 1
INSERT 478340 1
INSERT 478341 1
INSERT 478342 1
INSERT 478343 1
INSERT 478344 1
INSERT 478345 1
INSERT 478346 1
INSERT 478347 1
INSERT 478348 1
INSERT 478349 1
INSERT 478350 1
HTTP/1.0 200 OK
Server: AOLserver/3.4.2
MIME-Version: 1.0
Date: Mon, 20 Jan 2003 18:19:47 GMT
Content-Type: text/html
Content-Length: 3636
Connection: close

Request Error

psql:upgrade-4.5-4.6.sql:25: ERROR:  parser: parse error at or near "or"
psql:upgrade-4.5-4.6.sql:144: ERROR:  parser: parse error at or near "or"
psql:upgrade-4.5-4.6.sql:159: ERROR:  parser: parse error at or near "or"
psql:upgrade-4.5-4.6.sql:170: ERROR:  parser: parse error at or near "or"
psql:upgrade-4.5-4.6.sql:189: ERROR:  parser: parse error at or near "or"
psql:upgrade-4.5-4.6.sql:206: ERROR:  parser: parse error at or near "or"
psql:upgrade-4.5-4.6.sql:209: ERROR:  CreateTrigger: function content_item_search__utrg() does not exist
psql:upgrade-4.5-4.6.sql:247: ERROR:  parser: parse error at or near "or"

    invoked from within
"db_source_sql_file -callback $callback $path/$file_path"
    (procedure "apm_package_install_data_model" line 32)
    invoked from within
"apm_package_install_data_model -callback $callback -data_model_files $data_model_files $spec_file_path"
    (procedure "apm_package_install" line 19)
    invoked from within
"apm_package_install -install_path $install_path  -callback apm_ns_write_callback -load_data_model  -data_model_files $data_model_files $spec_file"
    ("foreach" body line 36)
    invoked from within
"foreach pkg_info $pkg_install_list {
    set spec_file [pkg_info_spec $pkg_info]
    if { [catch {
        array set version [apm_read_package_info_file $spe..."
    ("uplevel" body line 34)
    invoked from within
"uplevel {
          ad_page_contract {

    Installs the packages.

    @author Bryan Quinn (mailto:bquinn@arsdigita.com)
    @creation-date Mon Oct  9 00:22:31..."
    (procedure "code::tcl::/web/med_dev/packages/acs-admin/www/apm/packages-..." line 2)
    invoked from within
"code::tcl::$__adp_stub"
    invoked from within
"if { [file exists $__adp_stub.tcl] } {

      # ensure that data source preparation procedure exists and is up-to-date
      adp_init tcl $__adp_stub
..."
    ("uplevel" body line 3)
    invoked from within
"uplevel {

    if { [file exists $__adp_stub.tcl] } {

      # ensure that data source preparation procedure exists and is up-to-date
      adp_init t..."
    (procedure "adp_prepare" line 2)
    invoked from within
"adp_prepare "
    (procedure "template::adp_parse" line 30)
    invoked from within
"template::adp_parse [file root [ad_conn file]] {}"
    (procedure "adp_parse_ad_conn_file" line 7)
    invoked from within
"$handler"
    ("uplevel" body line 2)
    invoked from within
"uplevel $code"
    invoked from within
"ad_try {
        $handler
      } ad_script_abort val {
        # do nothing
      }"
    invoked from within
"rp_serve_concrete_file [ad_conn file]"
    (procedure "rp_serve_abstract_file" line 60)
    invoked from within
"rp_serve_abstract_file "$root/$path""
    ("uplevel" body line 2)
    invoked from within
"uplevel $code"
    invoked from within
"ad_try {
        rp_serve_abstract_file "$root/$path"
        set tcl_url2file([ad_conn url]) [ad_conn file]
        set tcl_url2path_info([ad_conn url]) [ad_conn path_inf..."

Collapse
2: Re: 4.6 upgrade questions (response to 1)
Posted by Jon Griffin on
Yes you need PG 7.2.x. We are now using create or replace in procs and that is your error.
Collapse
3: Re: 4.6 upgrade questions (response to 1)
Posted by Roberto Mello on
Yes, you need 7.2. 4.6 was released without a check for 7.2, so the bootstrap installer allowed install to start. I fixed this for 4.6.1.

I'll add this to the release notes (can't remember if it's there).

The release notes I added to https://openacs.org/doc/openacs-4/release-notes.html talk about migration. You're right that a reference to this should be in the install docs.

There are people that are using it under AOLserver versions other than 3.3ad13. If you're not using i18n and have all the modules (nsxml, nssha1, nsrewrite, nscache, etc.), it should work.

-Roberto

Collapse
4: Re: 4.6 upgrade questions (response to 3)
Posted by Roberto Mello on
BTW, if you are planning on moving your 4.5 databases to PG 7.2.3, to then upgrade to OpenACS 4.6, you should really read this document I wrote as a result of moving openacs.org from PG 7.1 to 7.2.3:

https://openacs.org/doc/misc/openacs-pg-migration.txt

-Roberto

Collapse
5: Re: 4.6 upgrade questions (response to 4)
Posted by Kevin Murphy on
Roberto,

Thanks for the info.  Re: openacs-pg-migration.txt, I have some more questions:

<blockquote> 6.2. Edit dump and remove the creation of all PL/pgSQL handlers
6.3. Also remove all statements that create int_* functions.
</blockquote>

Can you be more specific, and does it really matter if these are removed from the dump if they are being substituted for by commands in _int.sql and postgresql.sql?

I assume I should do the following:

*) Get rid of the creation of plpgsql_call_handler().  I guess I should also get rid of the creation of the procedural language plpgsql also?

*) I'm a bit confused about the second one.  Looking at _int.sql, I see a bunch of functions that don't occur in my dump at all.  Also, in postgresql.sql, I see various tree/key functions, the first of which happens to be int_to_tree_key().  Am I supposed to get rid of all the key and tree functions in the dump?

Thanks,
Kevin Murphy

Collapse
6: Re: 4.6 upgrade questions (response to 4)
Posted by Kevin Murphy on
Roberto et al,

When I loaded my dump (from which I removed just the two handler statements), I got the following unexplained error:

psql:media_db.dmp:731: ERROR:  No such attribute or function 'oid'

The relevant statement is this:

CREATE VIEW "user_col_comments" as SELECT upper(text(c.relname)) AS table_name, upper(text(a.attname)) AS column_name, d.description AS comments FROM pg_class c, (pg\
_attribute a LEFT JOIN pg_description d ON ((a.oid = d.objoid))) WHERE ((c.oid = a.attrelid) AND (a.attnum > 0));

What should I do about this?  The definition of the 7.1 user_col_comments view used by the view creation function in postgresql.org also doesn't work because of the 'missing' oid column.

I am still quite the openacs newbie, so I am out of my element here.  I'm lucky just to know how to do "find /web -name "*.sql" | xargs grep whatever".

Thanks,
Kevin

Collapse
7: Re: 4.6 upgrade questions (response to 6)
Posted by Roberto Mello on
Kevin,

It's probably something I overlooked when writing my pg-migration document.

user_col_comments is defined in packages/acs-kernel/sql/postgresql/postgresql.sql which you should have loaded before starting the migration (as per the migration doc).

So you should get an error, but the error should be that that view is already defined. If you're getting a different error, perhaps you forgot to load the postgresql.sql file.

-Roberto

Collapse
8: Re: 4.6 upgrade questions (response to 7)
Posted by Kevin Murphy on
Roberto,

Sorry to keep pestering.

Here's my error: psql:media-7.1.dmp:752: ERROR:  No such attribute or function 'oid'

I did indeed load postgresql.sql.

When you do:

grep ERROR errors.log | grep -v "already exists"

do you really only get three errors?

I get four:

psql:media-7.1.dmp:752: ERROR:  No such attribute or function 'oid'
psql:media-7.1.dmp:4690: ERROR:  parser: parse error at or near "SELECT"
psql:media-7.1.dmp:4698: ERROR:  parser: parse error at or near "SELECT"
psql:media-7.1.dmp:21336: ERROR:  Relation "party_approved_member_map" does not exist

Here is how I did my stuff.  I have PG 7.1 running on the usual port and PG 7.2 running on port 5424.  I rely on PGPORT to specify the port.  /web/med_dev is the OACS 4.6 tree.  My "media" database and site is OACS 4.5.

EXPORT:

nsadmin@linux:/web/med_dev> which pg_dump
/usr/local/pgsql/bin/pg_dump
nsadmin@linux:/web/med_dev> set | grep PG
nsadmin@linux:/web/med_dev> pg_dump -o media >media-7.1.dmp
nsadmin@linux:/tmp/pg> psql --version
psql (PostgreSQL) 7.1.3

- - -

IMPORT:

nsadmin@linux:/web/med_dev> . ~/pg723.sh
nsadmin@linux:/web/med_dev> set | grep PG
PGDATA=/usr/local/pgsql-7.2.3/data
PGLIB=/usr/local/pgsql-7.2.3/lib
PGPORT=5424
nsadmin@linux:/web/med_dev> export PGPATH=/usr/local/pgsql-7.2.3
nsadmin@linux:/web/med_dev> export PATH=$PGPATH/bin:$PATH
nsadmin@linux:/web/med_dev> which psql
/usr/local/pgsql-7.2.3/bin/psql
nsadmin@linux:/web/med_dev> dropdb med_dev
ERROR:  DROP DATABASE: database "med_dev" does not exist
dropdb: database removal failed
nsadmin@linux:/web/med_dev> createdb -U nsadmin med_dev
CREATE DATABASE
nsadmin@linux:/web/med_dev> psql -U nsadmin -f packages/acs-kernel/sql/postgresql/postgresql.sql med_dev
nsadmin@linux:/web/med_dev> psql -U nsadmin -f media-7.1.dmp med_dev 2> errors.log
nsadmin@linux:/web/med_dev> psql --version
psql (PostgreSQL) 7.2.3

-Kevin Murphy

Collapse
9: Re: 4.6 upgrade questions (response to 1)
Posted by Dave Bauer on
Kevin the oid error message you are receiving is due to
using the -o switch to pg_dump. Do not use that switch.

Also if you read here, you will need to recreate the party_approved_member_map view.

https://openacs.org/doc/misc/openacs-pg-migration.txt

Collapse
10: Re: 4.6 upgrade questions (response to 9)
Posted by Kevin Murphy on
Dave,

Thanks for replying, but I get the error regardless of whether I use pg_dump's -o switch or not (I just tried it).

Also, Roberto's migration instructions specifically indicate the -o switch.

I have been using his instructions, so I know about the views.  His instructions have some gaps in them; they are definitely more for experts than not, so I am wondering if I am missing something.

(This may be neither here nor there, but if I dump with -o and then import back into PG 7.1, I do not get the oid problem.)

I am being careful about using 7.1 or 7.2 binaries and environment variables as appropriate.

-Kevin

P.S.  Here's a script showing me redoing the whole procedure NOT using the -o switch:

Script started on Wed Jan 22 14:10:03 2003
nsadmin@linux:/web/med_dev> pg_dump -V
pg_dump (PostgreSQL) 7.1.3
Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
Portions Copyright (c) 1996 Regents of the University of California
Read the file COPYRIGHT to see the usage and distribution terms.
nsadmin@linux:/web/med_dev> pg_dump media >media-7.1.dmp
nsadmin@linux:/web/med_dev> . ~/pg723.sh
nsadmin@linux:/web/med_dev> which dropdb
/usr/local/pgsql-7.2.3/bin/dropdb
nsadmin@linux:/web/med_dev> dropdb med_dev
DROP DATABASE
nsadmin@linux:/web/med_dev> createdb -T template0 med_dev
CREATE DATABASE
nsadmin@linux:/web/med_dev> createdlang plpgsql med_dev
nsadmin@linux:/web/med_dev> psql -f postgresql-7.1.sql med_dev

(a bunch of CREATE's, etc, -- no errors)

nsadmin@linux:/web/med_dev> psql -f media-7.1.dmp med_dev 2>errors-no-o.log >/dev/null
nsadmin@linux:/web/med_dev> grep ERROR errors-no-o.log | grep -v "already exists"
psql:media-7.1.dmp:740: ERROR:  No such attribute or function 'oid'
psql:media-7.1.dmp:4678: ERROR:  parser: parse error at or near "SELECT"
psql:media-7.1.dmp:4686: ERROR:  parser: parse error at or near "SELECT"
psql:media-7.1.dmp:21324: ERROR:  Relation "party_approved_member_map" does not exist