Skip to content

Reorder triggers in structure.sql

Krasimir Angelov requested to merge ka/fix-structure-sql-order-for-triggers into master

What does this MR do and why?

There is a lot of work going on at the moment to migrate some tables to bigint PKs. These MRs are making pretty simalar changes to structure.sql, the only difference (other than the new columns added) being the generated name for the functions/triggers.

Two examples are

Sometimes when merging, git is managing to merge the changes into structure.sql, but not in the order that pg_dump will export the objects (which is alphabetical, unless there is some dependency between objects).

This MR reorders some objects to match the how pg_dump will export them so that we do not have changes to structure.sql after dump (e.g. after gdk update, or db:migrate), which makes working on the rest of the similar MRs tricky.

How to set up and validate locally

  1. On master, after executing bundle exec rails db:migrate, there will be a diff like
    diff --git a/db/structure.sql b/db/structure.sql
    index 4d5e58cd0727..cd2f9b4b4129 100644
    --- a/db/structure.sql
    +++ b/db/structure.sql
    +++ b/db/structure.sql
    @@ -234,7 +234,7 @@ BEGIN
     END;
     $$;
     
    -CREATE FUNCTION trigger_7f4fcd5aa322() RETURNS trigger
    +CREATE FUNCTION trigger_3207b8d0d6f3() RETURNS trigger
         LANGUAGE plpgsql
         AS $$
     BEGIN
    @@ -243,25 +243,25 @@ BEGIN
     END;
     $$;
     
    -CREATE FUNCTION trigger_c5a5f48f12b0() RETURNS trigger
    +CREATE FUNCTION trigger_7f4fcd5aa322() RETURNS trigger
         LANGUAGE plpgsql
         AS $$
     BEGIN
    -  NEW."note_id_convert_to_bigint" := NEW."note_id";
    +  NEW."id_convert_to_bigint" := NEW."id";
       RETURN NEW;
     END;
     $$;
     
    -CREATE FUNCTION trigger_c7107f30d69d() RETURNS trigger
    +CREATE FUNCTION trigger_c5a5f48f12b0() RETURNS trigger
         LANGUAGE plpgsql
         AS $$
     BEGIN
    -  NEW."id_convert_to_bigint" := NEW."id";
    +  NEW."note_id_convert_to_bigint" := NEW."note_id";
       RETURN NEW;
     END;
     $$;
    
    -CREATE FUNCTION trigger_3207b8d0d6f3() RETURNS trigger
    +CREATE FUNCTION trigger_c7107f30d69d() RETURNS trigger
         LANGUAGE plpgsql
         AS $$
     BEGIN
    @@ -33517,12 +33517,12 @@ CREATE TRIGGER projects_loose_fk_trigger AFTER DELETE ON projects REFERENCING OL
    
     CREATE TRIGGER trigger_1a857e8db6cd BEFORE INSERT OR UPDATE ON vulnerability_occurrences FOR EACH ROW EXECUTE FUNCTION trigger_1a857e8db6cd();
    
    +CREATE TRIGGER trigger_3207b8d0d6f3 BEFORE INSERT OR UPDATE ON ci_build_needs FOR EACH ROW EXECUTE FUNCTION trigger_3207b8d0d6f3();
    +
     CREATE TRIGGER trigger_7f4fcd5aa322 BEFORE INSERT OR UPDATE ON sent_notifications FOR EACH ROW EXECUTE FUNCTION trigger_7f4fcd5aa322();
    
     CREATE TRIGGER trigger_c5a5f48f12b0 BEFORE INSERT OR UPDATE ON epic_user_mentions FOR EACH ROW EXECUTE FUNCTION trigger_c5a5f48f12b0();
     
    -CREATE TRIGGER trigger_3207b8d0d6f3 BEFORE INSERT OR UPDATE ON ci_build_needs FOR EACH ROW EXECUTE FUNCTION trigger_3207b8d0d6f3();
    -
     CREATE TRIGGER trigger_c7107f30d69d BEFORE INSERT OR UPDATE ON merge_request_metrics FOR EACH ROW EXECUTE FUNCTION trigger_c7107f30d69d();
  2. On this branch, after executing bundle exec rails db:migrate, there should be no changes to structure.sql

MR acceptance checklist

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

Merge request reports