Forum OpenACS Q&A: can't delete an acs_object -> ERROR: update or delete on "acs_objects" violates foreign key constraint "acs_objects_context_id_fk" on "acs_objects"

Collapse
1: can't delete an acs_object -> ERROR: update or delete on "acs_objects" violates foreign key constraint "acs_objects_context_id_fk" on "acs_objects"
Posted by Lurch . on 06/19/05 03:21 AM
executing:
select content_folder__delete('2056',true);
gives the error message:
...
NOTICE: Deleting associated comments...
CONTEXT: PL/pgSQL function "content_item__delete" line 4 at perform
PL/pgSQL function "content_folder__del" line 51 at perform
PL/pgSQL function "content_folder__delete" line 5 at perform
NOTICE: Deleting content item...
CONTEXT: PL/pgSQL function "content_item__delete" line 4 at perform
PL/pgSQL function "content_folder__del" line 51 at perform
PL/pgSQL function "content_folder__delete" line 5 at perform
ERROR: update or delete on "acs_objects" violates foreign key constraint "acs_objects_context_id_fk" on "acs_objects"
DETAIL: Key (object_id)=(2056) is still referenced from table "acs_objects".

this appears to be the result of having a comment object/revision from general comments on this folder.

what is the best way to work around this problem? (the intention is to remove all information associated with the folder - including any comments or anything that may have been attached and is somehow related to it).

Thanks.

Collapse
2: Re: can't delete an acs_object -> ERROR: update or delete on "acs_objects" violates foreign key constraint "acs_objects_context_id_fk" on "acs_objects" (response to 1)
Posted by Lurch . on 06/24/05 09:33 PM
for the poor and unfortunate souls that might get screwed by this... it appears before calling content::folder::delete one must first do this magic incantation:

db_list bla {select acs_message__delete(object_id) from acs_objects where object_id in
(select comment_id from general_comments where object_id in
(select b.item_id from cr_items a, cr_items b
where b.tree_sortkey between a.tree_sortkey and tree_right(a.tree_sortkey)
and a.item_id = :item_id))}
Collapse
3: Re: can't delete an acs_object -> ERROR: update or delete on "acs_objects" violates foreign key constraint "acs_objects_context_id_fk" on "acs_objects" (response to 1)
Posted by Jun Yamog on 06/27/05 04:47 PM
I think general comments should trigger a delete when its parent object is being deleted. Maybe putting a on delete cascade is more proper.
Collapse
4: Re: can't delete an acs_object -> ERROR: update or delete on "acs_objects" violates foreign key constraint "acs_objects_context_id_fk" on "acs_objects" (response to 1)
Posted by Lurch . on 06/28/05 08:56 PM
The problem here is not directly related to comments though - it is related to the context that comments are in.

The acs_objects table has a context_id field which references object_id field in itself; this field is set for general comments for them to inherit permission from an object in a different hierarchy (the content folder that I am dealing with). When deleteing the cr_folder hierarchy, the comments are not part of the same hierarchy, so the CASCADE does nothing for them, however since the context_id still references the objects, the delete fails.

The right thing may be to set on delete cascade on context_id, but then a delete of an object in one hierarchy may cause deletes in others. in this case this is the correct thing to do (comments should get erased with the objects they are tacked on to) but I am not sure that this is true in the general case.