NULL safe integrations type maintainance trigger

What does this MR do and why?

This MR improves the integrations trigger that keeps type and type_new in-sync by ensuring that:

• it operates in both directions
• it is null safe

Migration details

This DB changes the integrations_set_type_new function so that if type is NULL, then we infer it from type_new, and if type_new is NULL, we infer it from type. Currently we unconditionally set type_new, even if a value was provided, and never set type.

CREATE OR REPLACE FUNCTION integrations_set_type_new() RETURNS TRIGGER AS $$
BEGIN
  UPDATE integrations
     SET type_new = COALESCE(NEW.type_new, regexp_replace(NEW.type, '\A(.+)Service\Z', 'Integrations::\1')),
         type = COALESCE(NEW.type, regexp_replace(NEW.type_new, '\AIntegrations::(.+)\Z', '\1Service'))
   WHERE integrations.id = NEW.id;
  RETURN NULL;
END $$ LANGUAGE PLPGSQL

Timings:

Migration: 20220211214605 - UpdateIntegrationsTriggerTypeNewOnInsertNullSafe


Type: Regular
Duration: 1.1 s
Database size change: +8.00 KiB

How to set up and validate locally

• At the database console, insert a value with a null type_new, but a defined type:

insert into integrations (type) values ('FooService');

• Insert a value with a null type, but a defined type_new:

insert into integrations (type_new) values ('Integrations::Bar');

Observe that the rows have the other values inferred.

select * from integrations;

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.

Related to #333508 (closed)