Jade, looking through notifications, it appears that the foreign keys to objects about which notifications are generated (eg forums_messages) *do* "on delete cascade":
in notification_requests:
-- The object this request pertains to
object_id integer
constraint notif_request_object_id_fk
references acs_objects (object_id)
on delete cascade,
and in notifications:
-- the object this notification pertains to
object_id integer
constraint notif_object_id_fk
references acs_objects(object_id)
on delete cascade,
However, the primary keys for these tables (request_id, notification_id) that have a foreign key constraint to acs_objects that *don't* on cascade delete. When these objects are deleted via the pgplsql functions, then their corresponding acs_objects get correctly deleted, but otherwise the acs_objects have to be manually deleted.
So, adding "on delete cascade" to the fk constraints should fix that, eh? Thus:
alter table notifications add constraint notif_notif_id_fk
foreign key (object_id)
references acs_objects (object_id)
on delete cascade;
alter table notification_requests add constraint notif_request_id_fk
foreign key (object_id)
references acs_objects (object_id)
on delete cascade;
Anyway, presuming this is so, I'll add a patch to my bug report that does this, including an upgrade script. Have a look and if (Jade/Jeff) you think this is correct, let me know and I'll go ahead and commit to oacs-5-1.
