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

Collapse
Posted by Don Baccus on
Well, that's very bizarre.  It is failing while dropping the old version of that view then fails when it tries to create the new one because ... the underlying rule already exists.

Very bizarre.

Before running the upgrade script (on a new restore of the dump of course) could you see what happens if you try selecting from the view?

There are problems going to PG 7.2->PG 7.3 if you use the default limits on identifier length because the PG group bumped it from 32 to 64.  About time, but it means they can't guarantee a dump from 7.2 will reload correctly into 7.3.  But that's not the problem you're seeing (there are some threads for getting around that problem when you get that far).

Hmmm ... did you check the output of the dump?  Are you sure there were no errors when the view was created on the restore?  It sure looks like the underlying rule was created but the view itself was not.

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