Skip to content

Add PG trigger to maintain `projects.has_external_wiki`

Luke Duncalfe requested to merge 290715-has_external_wiki_trigger into master

What does this MR do?

This MR adds a PostgreSQL trigger to maintain data for the projects.has_external_wiki column.

We cache whether a project has an "External Wiki" integration enabled in the has_external_wiki column on projects. It will be TRUE if the project has the "External Wiki" integration enabled.

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

== 20201214032220 AddHasExternalWikiTrigger: migrating ========================
-- execute("CREATE OR REPLACE FUNCTION set_has_external_wiki()\nRETURNS TRIGGER AS\n$$\nBEGIN\nUPDATE projects SET has_external_wiki = COALESCE(NEW.active, FALSE)\nWHERE projects.id = COALESCE(NEW.project_id, OLD.project_id);\nRETURN NEW;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
   -> 0.0075s
-- execute("CREATE TRIGGER trigger_has_external_wiki_on_insert\nAFTER INSERT ON services\nFOR EACH ROW\nWHEN (NEW.type = 'ExternalWikiService' AND NEW.project_id IS NOT NULL)\nEXECUTE FUNCTION set_has_external_wiki();\n")
   -> 0.0048s
-- execute("CREATE TRIGGER trigger_has_external_wiki_on_update\nAFTER UPDATE ON services\nFOR EACH ROW\nWHEN (NEW.type = 'ExternalWikiService' AND OLD.active != NEW.active AND NEW.project_id IS NOT NULL)\nEXECUTE FUNCTION set_has_external_wiki();\n")
   -> 0.0004s
-- execute("CREATE TRIGGER trigger_has_external_wiki_on_delete\nAFTER DELETE ON services\nFOR EACH ROW\nWHEN (OLD.type = 'ExternalWikiService' AND OLD.project_id IS NOT NULL)\nEXECUTE FUNCTION set_has_external_wiki();\n")
   -> 0.0003s
== 20201214032220 AddHasExternalWikiTrigger: migrated (0.0133s) ===============

Down

== 20201214032220 AddHasExternalWikiTrigger: reverting ========================
-- execute("DROP TRIGGER trigger_has_external_wiki_on_insert ON services;\n")
   -> 0.0084s
-- execute("DROP TRIGGER trigger_has_external_wiki_on_update ON services;\n")
   -> 0.0009s
-- execute("DROP TRIGGER trigger_has_external_wiki_on_delete ON services;\n")
   -> 0.0002s
-- execute("DROP FUNCTION set_has_external_wiki;\n")
   -> 0.0016s
== 20201214032220 AddHasExternalWikiTrigger: reverted (0.0114s) ===============

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Edited by Luke Duncalfe

Merge request reports