Add migration to fix approval_project_rules without protected branches

What does this MR do and why?

This MR adds a background migration to update approval_project_rules to set applies_to_all_protected_branches to true with scan_finding type. This inconsistency was due to a bug: behavior of branches clause in scan finding rul... (#383603 - closed) where applies_to_all_protected_branches was not passed to ApprovalRules::CreateService while creating approval_project_rules which was fixed as part of Ignore scan_finding rule for MR against unprote... (!105246 - merged) The background migration makes sure that older data are consistent.

Query

UPDATE approval_project_rules
SET applies_to_all_protected_branches = true
FROM (SELECT approval_project_rules.id
      AS approval_project_rules_id
      FROM approval_project_rules
      WHERE approval_project_rules.report_type = 4
      AND approval_project_rules.applies_to_all_protected_branches = false
      AND (NOT EXISTS(SELECT 1 FROM approval_project_rules_protected_branches WHERE approval_project_rules_protected_branches.approval_project_rule_id = approval_project_rules.id))
      AND approval_project_rules.id BETWEEN 5447026 AND 5447526) AS ids
WHERE approval_project_rules.id = ids.approval_project_rules_id;

Query plan

 ModifyTable on public.approval_project_rules  (cost=1.26..28.76 rows=1 width=201) (actual time=12.123..12.125 rows=0 loops=1)
   Buffers: shared hit=5 read=18 dirtied=3
   I/O Timings: read=11.962 write=0.000
   ->  Nested Loop Anti Join  (cost=1.26..28.76 rows=1 width=201) (actual time=12.122..12.123 rows=0 loops=1)
         Buffers: shared hit=5 read=18 dirtied=3
         I/O Timings: read=11.962 write=0.000
         ->  Nested Loop  (cost=0.84..22.35 rows=1 width=202) (actual time=12.121..12.122 rows=0 loops=1)
               Buffers: shared hit=5 read=18 dirtied=3
               I/O Timings: read=11.962 write=0.000
               ->  Index Scan using approval_project_rules_pkey on public.approval_project_rules approval_project_rules_1  (cost=0.42..18.91 rows=1 width=14) (actual time=12.120..12.120 rows=0 loops=1)
                     Index Cond: ((approval_project_rules_1.id >= 5447026) AND (approval_project_rules_1.id <= 5447526))
                     Filter: ((NOT approval_project_rules_1.applies_to_all_protected_branches) AND (approval_project_rules_1.report_type = 4))
                     Rows Removed by Filter: 16
                     Buffers: shared hit=5 read=18 dirtied=3
                     I/O Timings: read=11.962 write=0.000
               ->  Index Scan using approval_project_rules_pkey on public.approval_project_rules  (cost=0.42..3.44 rows=1 width=188) (actual time=0.000..0.000 rows=0 loops=0)
                     Index Cond: (approval_project_rules.id = approval_project_rules_1.id)
                     I/O Timings: read=0.000 write=0.000
         ->  Index Scan using approval_project_rules_protected_branches_pkey on public.approval_project_rules_protected_branches  (cost=0.42..3.44 rows=1 width=14) (actual time=0.000..0.000 rows=0 loops=0)
               Index Cond: (approval_project_rules_protected_branches.approval_project_rule_id = approval_project_rules_1.id)
               I/O Timings: read=0.000 write=0.000

Time: 14.970 ms
  - planning: 2.653 ms
  - execution: 12.317 ms
    - I/O read: 11.962 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 5 (~40.00 KiB) from the buffer pool
  - reads: 18 (~144.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 3 (~24.00 KiB)
  - writes: 0

Migration Output

➜ bundle exec rails db:migrate:up:main VERSION=20221130192239
main: == 20221130192239 FixApprovalProjectRulesWithoutProtectedBranches: migrating ==
main: == 20221130192239 FixApprovalProjectRulesWithoutProtectedBranches: migrated (0.0402s) 

➜ bundle exec rails db:migrate:down:main VERSION=20221130192239
main: == 20221130192239 FixApprovalProjectRulesWithoutProtectedBranches: reverting ==
main: == 20221130192239 FixApprovalProjectRulesWithoutProtectedBranches: reverted (0.0122s) 

Migration time calculation

Total number of records matching the condition: 8218

[ gprd ] production> ApprovalProjectRule.where(report_type: 4).where(applies_to_all_protected_branches: false).count
=> 8218

Total number of records to be updated: 2993

[ gprd ] production> ApprovalProjectRule.where(report_type: 4, applies_to_all_protected_branches: false).where('NOT EXISTS(SELECT 1 FROM approval_project_rules_protected_branches WHERE approval_project_rules_protec
ted_branches.approval_project_rule_id = approval_project_rules.id)').count
=> 2993

batch size = 1000 8218 / 1000 = ~8 batches

Estimated times per batch:

• 15ms for delete statement with 500 items (sub-batch)

Total: ~30ms per batch

2 mins delay per batch (safe for the given total time per batch)

8 batches * 2 min per batch = 16 mins to run all the scheduled jobs

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.