Comment 0 for bug 1747042

Evergreen 3.0+

As of version 2.11 reporter.hold_request_record is a table instead of a view. The table contains a row per action.hold_request. When a hold is purged from action.hold_request, the row persists in the reporter table.

Arguably, retaining the data for purged holds may be useful for reporting. However, when the table was created in 2.11 it only included data from action.hold_request. That's because the view it was replacing only cared about action.hold_request.

Either the rows should disappear when a hold is deleted (via purging) or the table should be back-filled with aged holds data for consistency.

Given the original VIEW only cared about active hold requests, deleting the reporter.hold_request_record row when a hold is deleted seems like a logical course of action.

Something along the lines of:

DELETE FROM reporter.hold_request_record WHERE id IN (/* holds not in ahr */)

ALTER TABLE reporter.hold_request_record
    ADD CONSTRAINT ahrfk FOREIGN KEY (id)
    REFERENCES action.hold_request (id) ON DELETE CASCADE;