Skip to content

Add PG trigger for has_external_issue_tracker

Luke Duncalfe requested to merge 290715-has_external_issue_tracker_trigger into master

What does this MR do?

This MR adds a PostgreSQL trigger to maintain data for the projects.has_external_issue_tracker column. It's very similar in nature to a MR that previously added a trigger for projects.has_external_wiki !49916 (merged).

We cache whether a project has an "External Issue Tracker" integration enabled in the has_external_issue_tracker column on projects. It should be TRUE if the project has an "External Issue Tracker" integration enabled. An active "External Issue Tracker" integration is a record in services that has a category of "issue_tracker" and is active.

This was previously maintained with application code.

The column is easy to fall out of consistency when we fail to maintain it during bulk operations (including PostgreSQL cascading deletes !48163 (merged)) as mentioned in:

As Ecosystem is increasingly changing integration data using bulk operations, switching the responsibility for maintaining the correct data away from application code and to a PostgreSQL trigger allows us to maintain these caches accurately.

In addition to the new triggers, this MR also removes the application logic that was previously maintaining the values of these columns.

Issue: #290715 (closed)

Migration output

Up

== 20210117210226 AddHasExternalIssueTrackerTrigger: migrating ================
-- execute("CREATE OR REPLACE FUNCTION set_has_external_issue_tracker()\nRETURNS TRIGGER AS\n$$\nBEGIN\nUPDATE projects SET has_external_issue_tracker = (\n  EXISTS\n  (\n    SELECT 1\n    FROM services\n    WHERE project_id = COALESCE(NEW.project_id, OLD.project_id)\n      AND active = TRUE\n      AND category = 'issue_tracker'\n    )\n  )\nWHERE projects.id = COALESCE(NEW.project_id, OLD.project_id);\nRETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
   -> 0.0069s
-- execute("CREATE TRIGGER trigger_has_external_issue_tracker_on_insert\nAFTER INSERT ON services\nFOR EACH ROW\nWHEN (NEW.category = 'issue_tracker' AND NEW.active = TRUE AND NEW.project_id IS NOT NULL)\nEXECUTE FUNCTION set_has_external_issue_tracker();\n")
   -> 0.0030s
-- execute("CREATE TRIGGER trigger_has_external_issue_tracker_on_update\nAFTER UPDATE ON services\nFOR EACH ROW\nWHEN (NEW.category = 'issue_tracker' AND OLD.active != NEW.active AND NEW.project_id IS NOT NULL)\nEXECUTE FUNCTION set_has_external_issue_tracker();\n")
   -> 0.0004s
-- execute("CREATE TRIGGER trigger_has_external_issue_tracker_on_delete\nAFTER DELETE ON services\nFOR EACH ROW\nWHEN (OLD.category = 'issue_tracker' AND OLD.active = TRUE AND OLD.project_id IS NOT NULL)\nEXECUTE FUNCTION set_has_external_issue_tracker();\n")
   -> 0.0004s
== 20210117210226 AddHasExternalIssueTrackerTrigger: migrated (0.0109s) =======

Down

== 20210117210226 AddHasExternalIssueTrackerTrigger: reverting ================
-- execute("DROP TRIGGER IF EXISTS trigger_has_external_issue_tracker_on_insert ON services")
   -> 0.0086s
-- execute("DROP TRIGGER IF EXISTS trigger_has_external_issue_tracker_on_update ON services")
   -> 0.0003s
-- execute("DROP TRIGGER IF EXISTS trigger_has_external_issue_tracker_on_delete ON services")
   -> 0.0002s
-- execute("DROP FUNCTION IF EXISTS set_has_external_issue_tracker()")
   -> 0.0017s
== 20210117210226 AddHasExternalIssueTrackerTrigger: reverted (0.0110s) =======

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Related to #290715 (closed)

Edited by Luke Duncalfe

Merge request reports