Forum OpenACS Q&A: Problem upgrading oacs-5.8 to oacs-5.9

Request notifications

After upgrading a 5.8 instance with several custom packages to 5.9 I found out that almost all my custom views joining to acs_objects had disappeared and I tracked down the problem to the kernel upgrade script upgrade-5.9.0d4-5.9.0d5.sql.

The script drops the tree_sortkey and max_child_sortkey columns from acs_objects and, given the use of 'cascade', also all the views making use of the two columns. I understand that this is necessary, but having some dozens of views to rebuild it's also quite annoying.

In my case I wrote a small before-upgrade script (luckily all my views share the same prefix) extracting the view definitions from pg_views, removing via regsub the two columns and saving them to a file that I then feeded to psql after the upgrade.

The trick worked except a single view calling the old tree_level function. I had to create a new orresponding function with a recursive query and I post the code here, in case it can be useful to anybody.

CREATE OR REPLACE FUNCTION tree_level(p_object_id integer)
  RETURNS integer AS $sql$                                                 
   result  integer := null;                                                       

     with recursive objects as ( 
        select i.object_id, i.context_id
        from acs_objects i
        where i.object_id = p_object_id
        select p.object_id, p.context_id
        from acs_objects p, objects s
        where p.object_id = s.context_id 
	select count(*) into result
	from objects;

    return result;

LANGUAGE 'plpgsql';

Posted by Gustaf Neumann on
yes, when custom views refer to dropped fields, custom upgrade scripts are needed to regenerate these views. This happens easily with wild-card views.

Many thanks for the constructive report!