Forum OpenACS Q&A: upgrading content repository package gives error

Hi

I have recently upgraded my OpenACS installation from 5.1.5 to 5.5. I am currently upgrading the installed applications & services in the server.
While upgrading the content-repository package, I received following error.

-------------------------------
[13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: 
  • Loading data model /var/www/open_qualex/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.2.0d1-5.2.0d2.sql... [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: define_function_args [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: ---------------------- [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: 1 [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: (1 row) [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: CREATE FUNCTION [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice:
  • [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice:
  • Loading data model /var/www/open_qualex/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.2.0d2-5.2.0d3.sql...

    [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: ALTER TABLE
    [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: ALTER TABLE
    [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: ALTER TABLE
    [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: ALTER TABLE
    [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice:

  • Loading data model /var/www/open_qualex/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.2.0d3-5.2.0d4.sql...

    [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: define_function_args [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: ---------------------- [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: 1 [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: (1 row) [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: define_function_args [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: ---------------------- [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: 1 [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: (1 row) [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: >Loading data model /var/www/open_qualex/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.2.0d4-5.2.0d5.sql...

    [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: define_function_args [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: ---------------------- [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: 1 [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: (1 row) [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: define_function_args [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: ---------------------- [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: 1 [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: (1 row) [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: define_function_args [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: ---------------------- [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: 1 [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: (1 row) [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: define_function_args [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: ---------------------- [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: 1 [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: (1 row) [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: define_function_args [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: ---------------------- [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: 1 [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: (1 row) [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: define_function_args [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: ---------------------- [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: 1 [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: (1 row) [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: define_function_args [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: ---------------------- [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: 1 [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: (1 row) [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: define_function_args [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: ---------------------- [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: 1 [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: (1 row) [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: define_function_args [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: ---------------------- [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: 1 [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: (1 row) [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: define_function_args [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: ---------------------- [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: 1 [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: (1 row) [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: define_function_args [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: ---------------------- [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: 1 [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: (1 row) [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: define_function_args [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: ---------------------- [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: 1 [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: (1 row) [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: define_function_args [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: ---------------------- [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: 1 [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: (1 row) [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: define_function_args [13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: ---------------------- 13/Nov/2009:15:41:40][7538.3071388560][-conn:76-] Notice: 1 ... [13/Nov/2009:15:41:42][7538.3071388560][-conn:76-] Notice: Loading data model /var/www/open_qualex/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.2.0d10-5.2.0d11.sql... [13/Nov/2009:15:41:42][7538.3071388560][-conn:76-] Notice: define_function_args [13/Nov/2009:15:41:42][7538.3071388560][-conn:76-] Notice: ---------------------- [13/Nov/2009:15:41:42][7538.3071388560][-conn:76-] Notice: 1 [13/Nov/2009:15:41:42][7538.3071388560][-conn:76-] Notice: (1 row) [13/Nov/2009:15:41:42][7538.3071388560][-conn:76-] Notice: [13/Nov/2009:15:41:42][7538.3071388560][-conn:76-] Notice: Loading data model /var/www/open_qualex/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.2.0d11-5.2.0d12.sql...

    [13/Nov/2009:15:41:42][7538.3071388560][-conn:76-] Error: apm_package_install: Error installing Content Repository version 5.5.0d3: psql:upgrade-5.2.0d11-5.2.0d12.sql:1: ERROR: relation "cr_revisions_publish_date_idx" already exists psql:upgrade-5.2.0d11-5.2.0d12.sql:1: ERROR: relation "cr_revisions_publish_date_idx" 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" invoked from within "if { $load_data_model_p } { apm_package_install_data_model -callback $callback -data_model_files $data_model_files $spec_file_path ..." ("uplevel" body line 55) invoked from within "uplevel $body " [13/Nov/2009:15:41:42][7538.3071388560][-conn:76-] Error:

    Failed to install Content Repository, version 5.5.0d3. The following error was generated:

    psql:upgrade-5.2.0d11-5.2.0d12.sql:1: ERROR: relation "cr_revisions_publish_date_idx" already exists

    --------------------------------------------------
    Can anybody help me in this regard?

    Thanks in advance

    Ratnakar

  • Collapse
    Posted by Gustaf Neumann on
    From 5.1 to 5.5 is a long way! The most simple fix is probably to drop the index and run the update script again.
    Collapse
    Posted by Ratnakar Sagare on
    Can u please specify which index to drop & how to drop it?

    I am sorry but I am newbie in databases.

    Thanks
    Ratnakar

    Collapse
    Posted by Gustaf Neumann on
    in psql:

    drop index cr_revisions_publish_date_idx;

    Collapse
    Posted by Dave Bauer on
    To expand a bit:

    [13/Nov/2009:15:41:42][7538.3071388560][-conn:76-] Error:

    Failed to install Content Repository, version 5.5.0d3. The following error was generated:

    psql:upgrade-5.2.0d11-5.2.0d12.sql:1: ERROR: relation "cr_revisions_publish_date_idx" already exists

    It shows that cr_revisions_publish_date_idx already exists when the upgrade script is trying to create it.

    You can safely drop the index since the upgrade script will recreate it.

    The command gustaf posted will work.

    drop index cr_revisions_publish_date_idx;

    You need to execute that from the Postgresql shell by running the command

    psql databasename from a command line where database name is whatever you called the database when it was created.

    TO learn more about indexes see http://www.postgresql.org/docs/8.2/interactive/sql-dropindex.html

    In general typing "postgresql drop index" or "postgresql whatever" into a search engine will bring up the appropriate documentation.

    Collapse
    Posted by Ratnakar Sagare on
    Hi,
    I tried this command & able to go forward from the error. but still the package installation is not successful & this time the error is like this:
    ------------------------------------------------------
    Error: apm_package_install: Error installing Content Repository version 5.5.0d3: psql:upgrade-5.2.0d16-5.2.0d17.sql:101: ERROR: column *NEW*.object_package_id does not exist
    CONTEXT: SQL statement "create rule images_r as on insert to imagesi do instead (
    update cr_dummy set val = (
    select content_revision__new(
    new.title,
    new.description,
    now(),
    new.mime_type,
    new.nls_language,
    case when new.text is null
    then new.data
    else new.text
    end,
    content_symlink__resolve(new.item_id),
    new.revision_id,
    now(),
    new.creation_user,
    new.creation_ip,
    new.object_package_id
    ));
    insert into images ( image_id, width, height ) values (cr_dummy.val, new.width, new.height); );"
    PL/pgSQL function "content_type__refresh_trigger" line 73 at execute statement
    SQL statement "SELECT content_type__refresh_trigger ( $1 )"
    PL/pgSQL function "inline_0" line 9 at select into variables

    psql:upgrade-5.2.0d16-5.2.0d17.sql:101: ERROR: column *NEW*.object_package_id does not exist
    CONTEXT: SQL statement "create rule images_r as on insert to imagesi do instead (
    update cr_dummy set val = (
    select content_revision__new(
    new.title,
    new.description,
    now(),
    new.mime_type,
    new.nls_language,
    case when new.text is null
    then new.data
    else new.text
    end,
    content_symlink__resolve(new.item_id),
    new.revision_id,
    now(),
    new.creation_user,
    new.creation_ip,
    new.object_package_id
    ));
    insert into images ( image_id, width, height ) values (cr_dummy.val, new.width, new.height); );"
    PL/pgSQL function "content_type__refresh_trigger" line 73 at execute statement
    SQL statement "SELECT content_type__refresh_trigger ( $1 )"
    PL/pgSQL function "inline_0" line 9 at select into variables

    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"
    invoked from within
    "if { $load_data_model_p } {
    apm_package_install_data_model -callback $callback -data_model_files $data_model_files $spec_file_path
    ..."
    ("uplevel" body line 55)
    invoked from within
    "uplevel $body "
    [19/Nov/2009:16:18:25][4763.3065854864][-conn:2-] Error:

    Failed to install Content Repository, version 5.5.0d3. The following error was generated:

    psql:upgrade-5.2.0d16-5.2.0d17.sql:101: ERROR: column *NEW*.object_package_id does not exist CONTEXT: SQL statement "create rule images_r as on insert to imagesi do instead ( update cr_dummy set val = ( select content_revision__new( new.title, new.description, now(), new.mime_type, new.nls_language, case when new.text is null then new.data else new.text end, content_symlink__resolve(new.item_id), new.revision_id, now(), new.creation_user, new.creation_ip, new.object_package_id )); insert into images ( image_id, width, height ) values (cr_dummy.val, new.width, new.height); );" PL/pgSQL function "content_type__refresh_trigger" line 73 at execute statement SQL statement "SELECT content_type__refresh_trigger ( $1 )" PL/pgSQL function "inline_0" line 9 at select into variables

    <font color="red">NOTE:</font> If the error comes from a sql script you may try to source it manually. When you are done with that you should revisit the APM and try again but remember to leave the manually souced sql scipts unchecked on the previous page.

    --------------------------------------------------------
    Can you please look into this?
    Thanks,
    Ratnakar
    Ratnakar,

    You will be able to look into this yourself.

    The error thrown is :

    ERROR: column *NEW*.object_package_id does not exist

    It was thrown when a database 'rule' was being set up:

    create rule images_r as on insert to imagesi do instead

    which roughly means "if anyone tries to insert data into imagesi, do what follows instead".

    The 'NEW.' part refers to data associated with a row that is about to be entered:

    http://www.postgresql.org/docs/8.3/static/plpgsql-trigger.html

    So what this means is that the object_package_id field is either not present in the new row to be added, or not present in the data model.

    That means that somewhere in the upgrade scripts between 5.1 and 5.5 this field has been added to the data model, but has not been added into the 5.5 upgrade script.

    I recommend you study each upgrade script (starting at 5.2) to work out where this field was added. You could then either add the field manually and re-run your 5.5 upgrade script, or even upgrade your CR to the version in which the field was added first and then upgrade to 5.5 afterwards.

    When you identify the appropriate details please post back here in case anyone else needs to upgrade to 5.5 from 5.1 in future. You could even suggest a patch to the core team if you felt so inclined.

    Regards
    Richard