Fixes for Oracle 19c: make query portable to Oracle by replacing USING by explicit ON joins
This fixed the following problem, where the problem is not easy to spot on first sight: [16/Feb/2022:13:26:09][12845.7efd2d3e1700][-sched:0:41:22-] Error: nsoracle.c:1367:OracleSelect: error in `OCIStmtExecute ()': ORA-00904: "NOTIFICATION_REQUESTS"."USER_ID": invalid identifier : SQL: : select notification_id, : notif_subject, : notif_text, : notif_html, : file_ids, : user_id, : request_id, : type_id, : delivery_method_id, : response_id, : notif_date, : notif_user, : acs_permission.permission_p(notification_requests.object_id, !>>>!notification_requests.user_id, 'read') as still_valid_p : from notifications inner join notification_requests using (type_id, object_id) : inner join acs_objects on (notification_requests.request_id = acs_objects.object_id) : left outer join notification_user_map using (notification_id, user_id) : where sent_date is null : and creation_date <= notif_date : and (notif_date is null or notif_date < current_timestamp) : and interval_id = :interval_id
One of the most expensive operations in large site is the cleanup for notification_requests in situations, where the user has lost permissions on an object, on which the user wanted to receive notifications. This check was performed previously in notification::sweep::cleanup_notifications via a permission check over all notification requests, which can be very costly on large sites. This change moves this cleanup into the actual notification sending, where the permissions have to be sent anyhow.