Forum .LRN Q&A: Forums and Notifications deleting threads problem in postgresql

Collapse
1: Forums and Notifications deleting threads problem in postgresql
Posted by Dave Bauer on 08/12/02 02:02 AM
When you delete a thread in forums on Postgresql, is does
notification::request::delete but the notification objects referring
to the message never get removed causing a foreign key error.

The notifications tables has "on delete cascade" on object_id. I
noticed that the message_id was referred to in one notification object
in object_id and another in response_id. The response_id one was
causing the fk violation.

Do the notification objects need to be explicity deleted?

Collapse
2: Response to Forums and Notifications deleting threads problem in postgresql (response to 1)
Posted by Andrew Grumet on 09/16/02 06:11 PM
This is a problem in Oracle too. The "on delete cascade" is problematic because it leaves hanging rows in the ACS_OBJECTS table. This query shows how many rows with object_type 'notification' do not have a corresponding row in the NOTIFICATIONS. 
SQL> select count(*) from (select n.notification_id from acs_objects o, notifications n where o.object_type = 'notification' and n.notification_id(+) = o.object_id) where notification_id is NULL;

  COUNT(*)
----------
         6
Collapse
3: Response to Forums and Notifications deleting threads problem in postgresql (response to 1)
Posted by Andrew Grumet on 09/16/02 06:13 PM
Oh, and we're also getting the fk error. The quick fix is to add an "on delete cascade" for the response_id column. Unfortunately this propagates some existing ugliness. I'd like to see a better solution. Perhaps the author of the notifications package will chime in.
Collapse
4: Response to Forums and Notifications deleting threads problem in postgresql (response to 1)
Posted by Ben Adida on 09/17/02 06:01 AM
Yon and I wrote notifications and we discussed this problem, although we
mostly addressed the notification_request objects and not the notifications.

This is an interesting issue: if a thread is deleted, does that mean the
notification objects that haven't yet been processed should be removed,
thereby preventing some people from receiving notifications (even if some
people got notified in the previous batch)? I guess the answer is yes, simply
because we might as well limit those who get notification of a dead thread.

The right solution seems to be to have notification::request::delete also
remove existing notification objects that match. Thoughts?

Collapse
5: Response to Forums and Notifications deleting threads problem in postgresql (response to 1)
Posted by Andrew Grumet on 09/17/02 02:23 PM
The challenge seems to be that there is no mechanism to notify the notifications package that a forum thread was deleted. In other words, (I'm pretty sure that...) notification::request::delete is never called because nobody is calling it. Doing so would entail either modifying the forums package to be notifications-aware or implementing an observer.

A simpler solution might be to change on delete cascade to on delete set null and make sure the code never sends out notifications that have a null entry for object_id or request_id. In addition, the sweeper would have to be modified to delete any notifications that have null values in one or both of these columns.

I think it's acceptable, if not preferable, to have partially-fulfilled requests for deleted threads.

Collapse
6: Response to Forums and Notifications deleting threads problem in postgresql (response to 1)
Posted by Andrew Grumet on 09/17/02 02:34 PM
Ah, my mistake, I see that notification::request::delete_all is being called from forum::message::delete. Sorry for not doing my homework. I haven't had a chance to dig further, but yeah if one of those procs isn't deleting at the object level it probably should be.
Collapse
7: Response to Forums and Notifications deleting threads problem in postgresql (response to 1)
Posted by Andrew Grumet on 09/17/02 02:45 PM
I can't look into this further at the moment, but here's some food for thought. Perhaps the answer can be found in the fact that notification::request::delete_all takes an object_id as its argument, but in the errors we see, it's the foreign key constraint on the response_id column that is causing the problem.
Collapse
8: Response to Forums and Notifications deleting threads problem in postgresql (response to 1)
Posted by Mohan Pakkurti on 10/02/02 05:13 PM
In the forums package the notification_requests are deleted first and then the message itself. 
# Remove the notifications
  notification::request::delete_all -object_id $message_id
# Remove the message
  db_exec_plsql delete_message {}
The message cannot be deleted when there is a notification referencing it. The notifications pertaining to the message need to be deleted before deletion of the message. Since we have message_id we can get a list of notifications that reference the message in the response_id and delete those notifications first. After that the message can be deleted.

One way to do this:

  • Add a query called message_notification_ids which selects the notification_id for an message_id
  • Call notification::delete to delete that notification
This can be done in notificaion::request::delete_all. delete_message would work just fine after that.

Any comments?

Collapse
9: Response to Forums and Notifications deleting threads problem in postgresql (response to 1)
Posted by Ben Adida on 10/02/02 11:38 PM
Mohan:

I think this is a good idea. Delete notification requests (which will stop the creation of new notifications), then notifications, then the message. Feel free to make that patch happen.

Collapse
10: Re: how i can remove forums using XML and asp (response to 4)
Posted by Nasir sultan choudhry on 04/21/03 05:36 PM
hello! i want to know the coding for the delelation of XML and asp forums.so any one know let me know plz.