Skip to content

Use a PostgreSQL trigger to maintain `has_external_issue_tracker` and `has_external_wiki` state

Context

We cache whether a project has an External Wiki integration enabled, or an External Issue Tracker integration enabled, in two columns on the projects PostgreSQL table:

  • has_external_issue_tracker - true if the project has any issue tracker integration (service) enabled (see caching method).
  • has_external_wiki - true if the project has the "External Wiki" integration (service) enabled (see caching method).

The purpose is to reduce the number of database lookups needed to build the side menu navigation (when projects have these particular kinds of integrations, they appear in the side menu navigation).

Problem

These caches are easy to fall out of consistency when we fail to maintain them during bulk operations (including PostgreSQL cascading deletes !48163 (merged)) as mentioned in:

As Ecosystem is increasingly changing integration data using bulk operations it would be ideal to find a solution that offers a robust way of maintaining these columns.

Improvements

This thread #273574 (comment 456689437) explored the idea of using PostgreSQL TRIGGERs to maintain the correct state of has_external_issue_tracker and has_external_wiki columns at all times.

See:

Proposal

With some help from database we hope to write a trigger migration.

The trigger would be row-level.

It would run on INSERT, UPDATE, DELETE, and TRUNCATE of the services table.

We understand on UPDATE that the trigger can fire conditionally on certain columns being part of the update, in which case, only firing on when the active, type or category columns are updated.

The SQL that would fire would behave like this (where ROW is the row that has executed the trigger):

-- **The following is very pseudo-SQL.**

-- This SQL would only need to run if ROW.project_id is not NULL and ROW.type = 'ExternalWikiService'
--
-- Set has_external_wiki for the associated project.
-- A project can only have one "external wiki service". 
--
UPDATE projects
  SET has_external_wiki = BOOL(ROW.active AND ROW.type = 'ExternalWikiService')
  WHERE id = ROW.project_id;

-- This SQL would only need to run if ROW.project_id is not NULL AND ROW.category = 'issue_tracker'
--
-- Set has_external_issue_tracker for the associated project.
-- A project can have many "external issue tracker" integrations.
-- 
-- If ROW.active is TRUE then we could:
UPDATE projects
  SET has_external_issue_tracker = BOOL(ROW.active AND ROW.category = 'issue_tracker')
  WHERE id = ROW.project_id;
-- Otherwise, if ROW.active is FALSE we need to check if any exist for the project that are active:
new_has_external_issue_tracker = EXISTS
        (SELECT 1
          FROM services
          WHERE project_id = OLD.project_id
            AND active = TRUE
            AND category = 'issue_tracker');

UPDATE projects
  SET has_external_issue_tracker = new_has_external_issue_tracker
  WHERE id = ROW.project_id;

Risks

Most of the risk considerations are around ensuring the trigger would be performant.

Tests

We might want to write some unit tests that the database trigger fires when we expect it to.

Edited by Luke Duncalfe