Skip to content

Backfill missing vulnerability dismissal information

Gregory Havenga requested to merge ghavenga_migrate_malformed_dismissed_vulnerabilities into master

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.

Edited by Gregory Havenga

Merge request reports