Forum OpenACS Development: Re: 5.0 upgrade process requires Tcl Calls

Collapse
Posted by Joel Aufrecht on
My steps this time:
  1. Inspect the current, live database:
    select * from acs_privilege_descendant_map_view;
    ERROR:  Relation "acs_privilege_descendant_map_view" does not exist
    
    \dv  acs_privilege_descendant_map_view
    No matching relations found.
    \dv acs_privilege_descendant_map_view
    No matching relations found
    
    drop rule _RETacs_privilege_descendant_ma;
    ERROR:  Rule or view "_retacs_privilege_descendant_ma" not found
    
  2. Restore a recent backup of the live database as a new development database. (All of this is within PG 7.2.)
  3. connect to the new dev database via psql
  4. Manually run the commands in /packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.1-4.6.2.sql via cut-paste. Everything works until
    drop view acs_privilege_descendant_map_view;
    NOTICE:  identifier "acs_privilege_descendant_map_view" will be truncated to "acs_privilege_descendant_map_view"
    ERROR:  view "acs_privilege_descendant_map_view" does not exist
    create view acs_privilege_descendant_map_view
    as select distinct h1.privilege, h2.child_privilege as descendant
       from acs_privilege_hierarchy_index h1, acs_privilege_hierarchy_index h2
       where h2.tree_sortkey between h1.tree_sortkey and tree_right(h1.tree_sortkey)
       union
       select privilege, privilege
       from acs_privileges;
    NOTICE:  identifier "acs_privilege_descendant_map_view" will be truncated to "acs_privilege_descendant_map_view"
    ERROR:  Attempt to insert rule "_RETacs_privilege_descendant_ma" failed: already exists
    
    select * from acs_privilege_descendant_map_view;
    NOTICE:  identifier "acs_privilege_descendant_map_view" will be truncated to "acs_privilege_descendant_map_view"
    ERROR:  Relation "acs_privilege_descendant_map_view" does not exist
    
Collapse
Posted by Jeff Davis on
Joel, I suspect the acs_privilege_descendant_map view needs to be dropped as well (I think the problem is that the end result is to have acs_privilege_descendant_map as a table and acs_privilege_descendant_map_view fails to create since the truncated name collides with one on the original view).
Collapse
Posted by Joel Aufrecht on
To recap: trying to upgrade a 4.6.1 site to 5.0:
  • Restore a recent backup of the live database as a new development database. (All of this is within PG 7.2.)
  • Install the 4.6.3 tarball in /var/lib/aolserver/openacs-4.6.3
  • Edit /var/lib/aolserver/openacs-4.6.3/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.1-4.6.2.sql to read:
    -- DRB: Fix the incredibly slow execution of acs_privilege__add_child()
    
    -- JA: to work around naming conflict that breaks this view drop/add:
    drop view acs_privilege_descendant_map;
    
    drop view acs_privilege_descendant_map_view;
    create view acs_privilege_descendant_map_view
    as select distinct h1.privilege, h2.child_privilege as descendant
       from acs_privilege_hierarchy_index h1, acs_privilege_hierarchy_index h2
       where h2.tree_sortkey between h1.tree_sortkey and tree_right(h1.tree_sortkey)
       union
       select privilege, privilege
       from acs_privileges;
    
  • Manually run this upgrade file from psql - success! - thanks Jeff!
  • Manually run the 4.6.2-4.6.3 kerner upgrade file - success!
  • Start the web site using the partially upgrade database and 4.6.3 filesystem. Front page fails:
    Request Error
    Server startup failed: Error during bootstrapping
    
    Database operation "0or1row" failed (exception NSDB, "Query was not a statement returning rows.")
    
    ERROR:  Relation "acs_privilege_descendant_map" does not exist
  • Found that table in a/var/lib/aolserver/openacs-4.6.3/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql, recreated it manually in psql:
    aufrecht-dev=# create table acs_privilege_descendant_map (
            privilege       varchar(100) not null
                            constraint acs_priv_hier_priv_fk
                            references acs_privileges (privilege),
            descendant      varchar(100) not null
                            constraint acs_priv_hier_child_priv_fk
                            references acs_privileges (privilege)
    
    );
    
    aufrecht-dev(# aufrecht-dev(# aufrecht-dev(# aufrecht-dev(# aufrecht-dev(# aufrecht-dev(# aufrecht-d\
    ev(# aufrecht-dev(# NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
    CREATE
    aufrecht-dev=# aufrecht-dev=#
    
  • Refreshed - no change, so restarted the site.
  • Front page fails with this error:
    Request Error
    Server startup failed: Error during bootstrapping
    
    Database operation "0or1row" failed (exception NSDB, "Query was not a statement returning rows.")
    
    ERROR:  Relation "acs_privilege_descendant_map" with OID 1305029 no longer exists
  • Collapse
    Posted by Jeff Davis on
    Joel, I looked at where acs_privilege_descendant_map gets turned into a table (instead of a view), its in upgrade-4.6-4.6.1.sql so I think you somehow missed that update which might explain why this is not working...
    Collapse
    Posted by Joel Aufrecht on
    New starting assumption: my database thinks it holds acs-kernel 4.6.1 but it is actually 4.6.
    1. Restore a recent backup of the live database as a new development database. (All of this is within PG 7.2.)
    2. Install the 4.6.3 tarball in /var/lib/aolserver/openacs-4.6.3
    3. Run /packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6-4.6.1.sql via psql
    4. Start the server, using the 4.6.3 tarball file tree and database-in-progress
    5. (Site home page loads okay!) Go to the APM and click Install
    6. Select only the kernel for Upgrade. Select both sql/postgresql/upgrade/upgrade-4.6.1-4.6.2.sql sql/postgresql/upgrade/upgrade-4.6.2-4.6.3.sql
    7. both scripts are run without errors. restart server.
    8. Site loads. At this point I have the choice of upgrading my other packages, upgrading the database to 7.3, or upgrading the kernel to 5.0. I back up the database.
    9. I decided to upgrade to PG 7.3 first, but the upgrade script failed with a bunch of errors like:
      ==================================================================
      looking for function party_approved_member__remove_o in oacs
      grep result:
      WARNING: unable to find the real function name of party_approved_member__remove_o
      so I moved on to upgrading the other packages instead. Several packages upgraded cleanly but Edit This page failed with:
      psql:upgrade-1.1d-1.2.sql:195: ERROR:  Cannot insert a duplicate key into unique index acs_object_types_pk