Forum OpenACS Q&A: Error running OpenACS 4.6.3 upgrade queries

I just tried to use the package manager in 4.6.3 to upgrade a site from openacs-4.6 to openacs-4.6.3 and received the error shown below.

There seems to be a missing view and a previously defined rule.

If I migrate the database as is to PostgreSQL 7.4 and run the pg_dmp file through the perl "fix all the function names script" will this fix it - or is there something else at work here?

Hoping for suggestions.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Request Error

Password: 
psql:upgrade-4.6.1-4.6.2.sql:237: ERROR:  view "acs_privilege_descendant_map_view" does not exist
psql:upgrade-4.6.1-4.6.2.sql:244: ERROR:  Attempt to insert rule "_RETacs_privilege_descendant_ma" failed: already exists

    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 103)
    invoked from within
"apm_package_install  -enable=$enable_p  -install_path $install_path  -callback apm_ns_write_callback  -load_data_model  -data_model_files $data_model_..."
    ("foreach" body line 45)
    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 36)
    invoked from within
"uplevel {
    	  ad_page_contract {

    Installs the packages.

    @author Bryan Quinn (bquinn@arsdigita.com)
    @creation-date Mon Oct  9 00:22:31..."
    (procedure "code::tcl::/web/milex/packages/acs-admin/www/apm/packages-in..." line 2)

Yes, PostgreSQL 7.2.3.

I am not sure whether this view is new to 4.6.3 and should have been created by a trigger during an earlier part of the upgrade process, or whether it should have been there in the 4.6 version beforehand.

As far as the function is concerned, I wondered if I can simply track down the function def for "_RETacs_privilege_descendant_ma(p)" in the upgrade script and comment it out - since it is already defined. Alternatively maybe I could delete the function before running the script.

Regards
Richard

I have come to the conclusion that this is not an issue with truncation because:

1) OpenACS4.6 and 4.6.3 are both compatible with PostgreSQL 7.2.1

2) If the function, table or view names were truncated, as long as they stay in PostgreSQL 7.2.1 they would work. It is only in the case of upgrading to PostgreSQL7.3.x and beyond that the problem would occur.

So that means that this view should have been created at some point either in OpenACS 4.6 or during the earlier part of the Kernel upgrade.

My suspicion is that the function is already defined because it is defined against the actual table 'acs_privilege_descendant_map' (from earlier version I guess).

So what I need to know is:

- Are my suppositions correct?
- Where SHOULD this view have been created?
- Where will I find the relevant SQL?
- Should I delete the function prior to upgrading?

Many Thanks
Richard

Looking throught the files it seems that the original view was acs_privilege_descendant_map. In the install script for OpenACS 4.6.3 Dan Wickstrom has restructured the datamodel to improve performance by creating acs_privilege_descendant_map as a table and generating acs_privilege_descendant_map_view.

There are two upgrade scripts - one from 4.6 to 4.6.1 and then one from 4.6.1 to 4.6.3.

The new view acs_privilege_descendant_map_view is created in the first upgrade script. The second upgrade script then drops this view so that it can re-create it.

The most obvious conclusion therefore is that it is the 'drop view' command in the second upgrade script that is failing because the view does not exist. This would be consistent with the first upgrade script not having executed at all.

Looking through the output into the browser I can see no evidence of the upgrade-4.6-4.6.1.sql script having been executed, the only entries are from the 4.6.1 to 4.6.3 script.

How do I tell the package manager to run the first script before running the second? (I could copy and paste its contents but I don't want to have this happen in other packages as well). I feel sure that there will be an elegant way to do this.

Regards
Richard

Tried applying them from the command line but of course package manager then doesn't know that they have been applied.

So I tried the package manager installer again and now I see the problem. The installer only offers me the 4.6.1 to 4.6.2 and the 4.6.2 to 4.6.3 upgrade files.

So to fix this I need to get the package manager to offer me the 4.6 to 4.6.1 upgrade.

Any ideas?

Resolved.

Applying the upgrade upgrade-4.6-4.6.1 script to the database from the command line like so:

psql -f ./packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6-4.6.1.sql service0 > upgrade-4.6-4.6.1.log 2>&1

and then starting the service and doing the other two scripts from the package manager installer works fine.