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.