Backfill missing vulnerability dismissal information
What does this MR do and why?
This background migration backfills missing vulnerability dismissal information as a result of the bug in Dismissing a finding from the pipeline modal do... (#412983 - closed) • Mehmet Emin INAC, Gregory Havenga • 16.2 • On track. It does this by retrieving the dismissed_at
and dismissed_by_id
information from the related vulnerability_state_transition records created_at
and author_id
.
Related to #417937 (closed)
Queries
Collapsed For Brevity
Each Sub Batch
SELECT
"vulnerabilities".*
FROM
"vulnerabilities"
WHERE
"vulnerabilities"."id" BETWEEN 75534063 AND 75534563
AND "vulnerabilities"."state" = 2
AND "vulnerabilities"."dismissed_at" IS NULL
AND "vulnerabilities"."dismissed_by_id" IS NULL
AND "vulnerabilities"."id" >= 75534063
AND "vulnerabilities"."id" < 75534563
Time: 6.197 ms
- planning: 4.623 ms
- execution: 1.574 ms
- I/O read: 1.447 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 15 (~120.00 KiB) from the buffer pool
- reads: 2 (~16.00 KiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/20393/commands/66587
Bulk Vulnerability Selection
EXPLAIN
SELECT
*
FROM
vulnerability_state_transitions
WHERE
"vulnerability_state_transitions"."vulnerability_id" IN (
SELECT
"vulnerabilities".id
FROM
"vulnerabilities"
WHERE
"vulnerabilities"."id" BETWEEN 55618752 AND 75356499
AND "vulnerabilities"."state" = 2
AND "vulnerabilities"."dismissed_at" IS NULL
AND "vulnerabilities"."dismissed_by_id" IS NULL
AND "vulnerabilities"."id" >= 55618752
AND "vulnerabilities"."id" < 75356499)
AND "vulnerability_state_transitions"."to_state" = 2
AND NOT EXISTS (
SELECT
1
FROM
vulnerability_state_transitions AS vst
WHERE
vst.created_at > "vulnerability_state_transitions"."created_at"
AND "vst"."to_state" = 2
AND "vst"."vulnerability_id" = "vulnerability_state_transitions"."vulnerability_id")
Time: 28.769 ms
- planning: 2.335 ms
- execution: 26.434 ms
- I/O read: 12.091 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 4017 (~31.40 MiB) from the buffer pool
- reads: 6 (~48.00 KiB) from the OS file cache, including disk I/O
- dirtied: 1 (~8.00 KiB)
- writes: 0
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/20393/commands/66610
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.