Forum OpenACS Q&A: acs-content-repository 5.2.0-5.2.2 upgrade fails

Greetings OpenACSers,

When performing a test upgrade of our site from 5.2.0 to 5.2.2 the CR package failed with the following output:

Installing packages...

Installing Content Repository 5.2.2

* Installing data model for Content Repository 5.2.2...
o Loading data model /var/lib/aolserver/bmm/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.2.1d1-5.2.1d2.sql...

UPDATE 0
UPDATE 0
CREATE FUNCTION
define_function_args

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

Invalid switch: "----------------------"

Now, i have no idea where the invalid switch error is coming from, although i've seen it before when a package install/upgrade fails so i suspect it's a bug in the error-handling/reporting code (like a proc being called without a '--' before its args). Nor do i understand why this file appears to be failing after the first 4 statements, as when i source the file manually statement 5 works fine and it fails further down towards the end:

ERROR: column "revision_id" duplicated
CONTEXT: SQL statement "create view CR_REVISIONSi as select acs_objects.object_id,
acs_objects.object_type,
acs_objects.title as object_title,
acs_objects.package_id as object_package_id,
acs_objects.context_id,
acs_objects.security_inherit_p,
acs_objects.creation_user,
acs_objects.creation_date,
acs_objects.creation_ip,
acs_objects.last_modified,
acs_objects.modifying_user,
acs_objects.modifying_ip,
acs_objects.tree_sortkey,
acs_objects.max_child_sortkey, cr.revision_id, cr.title, cr.item_id,
content_revision__get_content(cr.revision_id) as data,
cr_text.text_data as text,
cr.description, cr.publish_date, cr.mime_type, cr.nls_language, CR_REVISIONS.* from acs_objects, cr_revisions cr, cr_text, CR_REVISIONS where
acs_objects.object_id = cr.revision_id and acs_objects.object_id = CR_REVISIONS.REVISION_ID"
PL/pgSQL function "content_type__refresh_view" line 53 at execute statement
SQL statement "SELECT content_type__refresh_view( $1 )"
PL/pgSQL function "inline_0" line 14 at perform

This error is the result of this block of code:

create or replace function inline_0() returns integer as '

declare v_row record;

begin

for v_row in select o.object_type,o.table_name
from acs_object_type_supertype_map m,
acs_object_types o
where (m.ancestor_type=''content_revision''
and o.object_type=m.object_type)
or (o.object_type=''content_revision'')
loop
if table_exists(v_row.table_name) then
perform content_type__refresh_view(v_row.object_type);
perform content_type__refresh_trigger(v_row.object_type);
end if;
end loop;
return 0;
end;' language 'plpgsql';

select inline_0();

This select statement on my system returns 100 rows, most of which are 'content_revision','cr_revisions' (the cr_revisions[ix] views are well and truely refreshed!).

The above error occurs when content_type__refresh_view is called with the 'acs_message_revision' object type. The acs_object_types table lists this type as using the table CR_REVISIONS (note uppercase). As first i thought this was the bug, as the table_name field is meant to be unique and already contains the cr_revisions (lowercase) entry for type content_revision - but this appears intentional? (shudder)

For reference, my content_type__refresh_view is currently defined as:

CREATE OR REPLACE FUNCTION content_type__refresh_view("varchar")
RETURNS int4 AS
$BODY$
declare
refresh_view__content_type alias for $1;
cols varchar default '';
tabs varchar default '';
joins varchar default '';
v_table_name varchar;
join_rec record;
begin
-- select
-- table_name, id_column, level
-- from
-- acs_object_types
-- where
-- object_type <> 'acs_object'
-- and
-- object_type <> 'content_revision'
-- start with
-- object_type = refresh_view__content_type
-- connect by
-- object_type = prior supertype

for join_rec in select ot2.table_name, ot2.id_column, tree_level(ot2.tree_sortkey) as level
from acs_object_types ot1, acs_object_types ot2
where ot2.object_type <> 'acs_object'
and ot2.object_type <> 'content_revision'
and ot1.object_type = refresh_view__content_type
and ot1.tree_sortkey between ot2.tree_sortkey and tree_right(ot2.tree_sortkey)
order by ot2.tree_sortkey desc
LOOP
cols := cols || ', ' || join_rec.table_name || '.*';
tabs := tabs || ', ' || join_rec.table_name;
joins := joins || ' and acs_objects.object_id = ' ||
join_rec.table_name || '.' || join_rec.id_column;
end loop;

select table_name into v_table_name from acs_object_types
where object_type = refresh_view__content_type;

if length(v_table_name) > 25 then
raise exception 'Table name cannot be longer than 25 characters, because that causes conflicting rules when we create the views.';
end if;

-- create the input view (includes content columns)

if table_exists(v_table_name || 'i') then
execute 'drop view ' || v_table_name || 'i';
end if;

-- FIXME: need to look at content_revision__get_content. Since the CR
-- can store data in a lob, a text field or in an external file, getting
-- the data attribute for this view will be problematic.

execute 'create view ' || v_table_name ||
'i as select acs_objects.object_id,
acs_objects.object_type,
acs_objects.title as object_title,
acs_objects.package_id as object_package_id,
acs_objects.context_id,
acs_objects.security_inherit_p,
acs_objects.creation_user,
acs_objects.creation_date,
acs_objects.creation_ip,
acs_objects.last_modified,
acs_objects.modifying_user,
acs_objects.modifying_ip,
acs_objects.tree_sortkey,
acs_objects.max_child_sortkey, cr.revision_id, cr.title, cr.item_id,
content_revision__get_content(cr.revision_id) as data,
cr_text.text_data as text,
cr.description, cr.publish_date, cr.mime_type, cr.nls_language' ||
cols ||
' from acs_objects, cr_revisions cr, cr_text' || tabs || ' where
acs_objects.object_id = cr.revision_id ' || joins;

-- create the output view (excludes content columns to enable SELECT *)

if table_exists(v_table_name || 'x') then
execute 'drop view ' || v_table_name || 'x';
end if;

execute 'create view ' || v_table_name ||
'x as select acs_objects.object_id,
acs_objects.object_type,
acs_objects.title as object_title,
acs_objects.package_id as object_package_id,
acs_objects.context_id,
acs_objects.security_inherit_p,
acs_objects.creation_user,
acs_objects.creation_date,
acs_objects.creation_ip,
acs_objects.last_modified,
acs_objects.modifying_user,
acs_objects.modifying_ip,
acs_objects.tree_sortkey,
acs_objects.max_child_sortkey, cr.revision_id, cr.title, cr.item_id,
cr.description, cr.publish_date, cr.mime_type, cr.nls_language,
i.name, i.parent_id' ||
cols ||
' from acs_objects, cr_revisions cr, cr_items i, cr_text' || tabs ||
' where acs_objects.object_id = cr.revision_id
and cr.item_id = i.item_id' || joins;

PERFORM content_type__refresh_trigger(refresh_view__content_type);

-- exception
-- when others then
-- dbms_output.put_line('Error creating attribute view or trigger for'
-- || content_type);

return 0;
end;$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Also, if i'm reading cvs correctly, the offending file (upgrade-5.2.1d1-5.2.1d2.sql) appears to be removed from HEAD?

As you can tell, i'm rather lost - thanks for any help you can offer!

Collapse
Posted by Dave Bauer on
Hi.

How did you install 5.2.0? It appears that your install of 5.2.0 is broken.

Try running the file found in packages/sql/postgresql/upgrade/upgrade-5.2.0a1-5.2.0a2.sql

This fixes this problem. It looks like either 5.2.0 was released with broken code, or you installed a pre-released version of 5.2.

Collapse
Posted by B Charles on
Hi Dave,

The 5.2.0 installation was a fresh install, so i guess it was released broken.

Sourcing upgrade-5.2.0a1-5.2.0a2.sql fixed the problem, thanks.