Forum OpenACS Q&A: Problem deleting content item

Posted by Peter Alberer on 01/17/02 05:15 PM

Hi,

i am having problems to delete a content item. i do not think that my 
problem is related to openacs but rather to my little knowledge about 
postgres, so i hope someone of you can help me. 

short description of the problem:

i can delete all of the revisions of an item and the item itself if 
the statements are individually entered in psql. but when i define a 
function that should execute both statements then there is a problem:

----------------------------------------------------------------

openacs=# create function test_f_0 ()
openacs-# returns integer as '
openacs'# begin
openacs'# perform lr_exam__delete_revision(19393);
openacs'# perform content_item__delete(19392);
openacs'# return 0;
openacs'# end;' language 'plpgsql';
CREATE

openacs=# select test_f_0 ();

NOTICE:  deleting exam_elements
NOTICE:  deleting exam_grades
NOTICE:  deleting exam
NOTICE:  deleting collection
NOTICE:  Deleting symlinks...
NOTICE:  Unscheduling item...
NOTICE:  Deleting associated revisions...
NOTICE:  Deleting associated item templates...
NOTICE:  Deleting item relationships...
NOTICE:  Deleting child relationships...
NOTICE:  Deleting parent relationships...
NOTICE:  Deleting associated permissions...
NOTICE:  Deleting keyword associations...
NOTICE:  Deleting associated comments...
NOTICE:  Deleting content item...
ERROR:  cr_items_item_id_fk referential integrity violation - key 
referenced from cr_items not found in acs_objects

-------------------------------------------------------------------

openacs=# select lr_exam__delete_revision(19393);

NOTICE:  deleting exam_elements
NOTICE:  deleting exam_grades
NOTICE:  deleting exam
NOTICE:  deleting collection
 lr_exam__delete_revision
--------------------------
                        0
(1 row)

openacs=# select content_item__delete(19392);

NOTICE:  Deleting symlinks...
NOTICE:  Unscheduling item...
NOTICE:  Deleting associated revisions...
NOTICE:  Deleting associated item templates...
NOTICE:  Deleting item relationships...
NOTICE:  Deleting child relationships...
NOTICE:  Deleting parent relationships...
NOTICE:  Deleting associated permissions...
NOTICE:  Deleting keyword associations...
NOTICE:  Deleting associated comments...
NOTICE:  Deleting content item...
 content_item__delete
----------------------
                    0
(1 row)


is there some kind of trigger involved that i do not know about and 
that does not get fired because of the transaction within the 
function ?

TIA!

2: Response to Problem deleting content item (response to 1)

Posted by Jun Yamog on 01/23/02 03:31 AM

Hi Peter,

That is indeed odd. Although from what remember for each content item you have an entry to acs_objects, cr_items and cr_revisions. So I would think that test_f_0 is the one that is behaving properly if the integrity constraint is present. So in deleting you have to delete the cr_revisions entry then the cr_items and finally the acs_objects. Although I have never done any deletes since from what I remeber it was problematic so I just followed Luke P's example for having a trash folder in cr_folders. Then move the deleted items to this trash folder. Try to see if an acs_object entry is created when you create a content item.

3: Response to Problem deleting content item (response to 1)

Posted by Peter Alberer on 01/23/02 09:29 AM

I managed to get the delete to work properly by using more delete cascades between my tables. Well, in fact i must admin that i do not really know why the above does not work and after my changes it works.

There IS a delete cascade between content_items.item_id and acs_objects.object_id, so the above error should, in my opinion never happen. Unfortunately i was not able to reproduce this problem with some simple test_tables.

The function content_item__delete does definitely remove all the revisions. It does not remove the content_item directly but relies on the delete cascade from acs_objects. (but this did not cause the problem, i changed that temporarily)

I do not think that my test function behaves properly as a "copy cr_items to /tmp/cr_items.txt" right before the line that produces the error creates a file that definitely has no remaining entry that could cause an error.