Forum OpenACS Q&A: acs-content-repository 5.2.0-5.2.2 upgrade fails
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!
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.
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.