Forum OpenACS Q&A: content_item__delete referential integrity error

1: content_item__delete referential integrity error

Posted by tammy m on 06/20/03 09:39 PM

I'm getting a referential integrity violation when trying to delete a content item with content_item__delete. Why?

openacs-dev=# select content_item__delete(6414) from dual \g
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:  Referential Integrity: attempting to delete latest_revision: 6415

Please help! thanks.

2: Re: content_item__delete referential integrity error (response to 1)

Posted by tammy m on 06/20/03 11:34 PM

Well no one answered me and I got impatient. Surprise!

So I did this:

update cr_items set latest_revision = null where content_type like 'orp_%' \g

It's ok I think since I'm deleting all items of that particular content type;)

Then I was able to issue select content_item__delete(6414) from dual \g no problem.

Yet my question remains... Is this the right way to go about deleting content items? It seems like if so, that content_item__delete should just do this for me as well. So I must be missing something.

Can someone please enlighten me on the right way to delete content items I have lying around the db that I don't want anymore? Are there more steps than calling content_item__delete?

thanks again

3: Re: content_item__delete referential integrity error (response to 1)

Posted by Robert Locke on 06/21/03 06:28 AM

Hi Tammy,

Sorry, meant to reply sooner. Which version of OACS and PG are you using? I seem to remember having this same problem in a previous version of OACS and solving it the way you did: nulling out the live/latest revision of the content item before deleting it. Or maybe I removed all the associated revisions before calling content_item__delete. I honestly can't remember, but you get the idea.

For the record, on my test OACS 4.6.3 (final)/PG 7.2.4 install, everything seems to work fine. I created a content_item with 3 revisions, and was able to successfully delete it without having to do anything special:

# select live_revision, latest_revision from cr_items where item_id = 383;
live_revision | latest_revision
---------------+-----------------
385 | 386
(1 row)

# select content_item__delete(383);
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)

# select * from cr_items where item_id = 383;
(0 rows)

4: Re: content_item__delete referential integrity error (response to 3)

Posted by tammy m on 06/21/03 07:31 PM

Thanks Robert.

I tried deleting the revisions first but that gave me more referential integrity errors...

But anyway I was able to delete in the end. Just wanted to be sure my nulling out latest revisions idea wasn't gonna bite me back one day;)

I'm on 4.6.1 and postgesql 7.2.4.

I'm loathe to upgrade to 4.6.3 cuz I had such a hard time getting my first install of OACS to work with all the packages I wanted... but I will one day;)

5: Re: content_item__delete referential integrity error (response to 1)

Posted by Jun Yamog on 06/23/03 09:27 AM

Hi Tammy,

Normally I would delete the revisions first.

Something like

select content_revision__delete(revision_id) from cr_revisions where item_id = :to_be_deleted_item;

Then you delete the content item

select content_item__delete(:to_be_deleted_item);

Although as mentioned by Rob, it seems to already work well in the latest version.