Forum OpenACS Q&A: null tree_sortkey on upgrading acs-kernel

Collapse
1: null tree_sortkey on upgrading acs-kernel
Posted by Justis Peters on 02/20/13 05:43 AM
We are attempting to upgrade a very old OpenACS installation from 5.1.2 to 5.7.0. On upgrading acs-kernel, we get the following error at the end of the upgrade: 
    Loading data model /var/lib/aolserver/caryoil/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.7.0d10-5.7.0d11.sql...

        CREATE FUNCTION
        CREATE FUNCTION
        CREATE FUNCTION
        CREATE FUNCTION

Failed to install Kernel, version 5.7.0. The following error was generated:

    Database operation "0or1row" failed (exception ERROR, "ERROR: null value in column "tree_sortkey" violates not-null
 constraint CONTEXT: SQL statement "insert into acs_objects 
(object_id, object_type, title, package_id, context_id, creation_date, creation_user, creation_ip, security_inherit_p) 
values ( $1 , $2 , $3 , $4 , $5 , $6 , $7 , $8 , $9 )" PL/pgSQL function "acs_object__new" line 39 at SQL 
statement PL/pgSQL function "apm_package_version__new" line 25 at assignment
Does anyone have suggestions on how to move past this? I have modified the upgrade scripts slightly, but only to comment out dropping constraints, triggers, and tables which do not yet exist. Thanks in advance for any pointers. Kind regards, Justis
Collapse
2: Re: null tree_sortkey on upgrading acs-kernel (response to 1)
Posted by Victor Guerra on 02/20/13 12:31 PM
Hi Justis,

actually there is a trigger on the acs_objects table ( acs_objects_insert_tr ) which executes before inserting, that trigger takes care of calculating the tree_sortkey. Are you probably dropping that trigger during the upgrade process? Maybe you can quickly check if the trigger is still there after running the whole upgrade process?. I quickly grep'd the upgrade scripts and I don't see any obvious code that would drop that trigger.

Best,

Collapse
3: Re: null tree_sortkey on upgrading acs-kernel (response to 2)
Posted by Justis Peters on 02/20/13 05:41 PM
Thanks, Victor. I think that's the issue. We're testing this on a copy of the database and it appears that the triggers on rel_constraints somehow didn't get restored.

Kind regards,
Justis

Collapse
4: Re: null tree_sortkey on upgrading acs-kernel (response to 3)
Posted by Justis Peters on 02/21/13 05:31 AM
Yes, that was the issue. Thanks again for the pointer.