Skip to content

Replace undefined confidence with unknown severity for vulnerabilities

Can Eldem requested to merge remove-undefined-confidence-vulnerability into master

What does this MR do?

We are deprecating undefined value from the confidence level and replace with unknown. #211502 (closed)

Application logic changed in this MR

!25884 (merged)

This Mr updates existing records

Cleanup issue

#217116 (closed)

Output of migration

UP

{{== 20200511092714 UpdateUndefinedConfidenceFromVulnerabilities: migrating =====
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerabilities, :id, {:where=>"confidence = 0", :name=>"index_vulnerability_on_id_and_confidence_eq_zero", :algorithm=>:concurrently})
   -> 0.0047s
-- add_index(:vulnerabilities, :id, {:where=>"confidence = 0", :name=>"index_vulnerability_on_id_and_confidence_eq_zero", :algorithm=>:concurrently})
   -> 0.0027s
== 20200511092714 UpdateUndefinedConfidenceFromVulnerabilities: migrated (0.0229s)}}

DOWN

== 20200511092714 UpdateUndefinedConfidenceFromVulnerabilities: reverting =====
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerabilities, :id, {:where=>"confidence = 0", :name=>"index_vulnerability_on_id_and_confidence_eq_zero", :algorithm=>:concurrently})
   -> 0.0048s
-- remove_index(:vulnerabilities, {:where=>"confidence = 0", :name=>"index_vulnerability_on_id_and_confidence_eq_zero", :algorithm=>:concurrently, :column=>:id})
   -> 0.0075s
== 20200511092714 UpdateUndefinedConfidenceFromVulnerabilities: reverted (0.0126s)

Query

exec CREATE INDEX index_vulnerability_on_id_and_confidence_eq_zero ON public.vulnerabilities USING btree (id) WHERE (confidence = 0)

The query has been executed. Duration: 1.241 s (edited)

explain SELECT Count(id) FROM vulnerabilities WHERE confidence = 0
Aggregate  (cost=3979.09..3979.10 rows=1 width=8) (actual time=25.917..25.917 rows=1 loops=1)
   Buffers: shared hit=1314 read=271
   I/O Timings: read=4.407
   ->  Index Only Scan using index_vulnerability_on_id_and_confidence_eq_zero on public.vulnerabilities  (cost=0.29..3760.41 rows=87475 width=8) (actual time=0.196..16.767 rows=87602 loops=1)
         Heap Fetches: 127
         Buffers: shared hit=1314 read=271
         I/O Timings: read=4.407

https://explain.depesz.com/s/Jx4d

explain UPDATE "vulnerabilities" SET  "confidence" = 2 WHERE "vulnerabilities"."confidence" = 0 AND "vulnerabilities"."id" BETWEEN 0 AND 1000
ModifyTable on public.vulnerabilities  (cost=0.29..80.85 rows=49 width=291) (actual time=0.014..0.014 rows=0 loops=1)
   Buffers: shared hit=2
   ->  Index Scan using index_vulnerability_on_id_and_confidence_eq_zero on public.vulnerabilities  (cost=0.29..80.85 rows=49 width=291) (actual time=0.012..0.012 rows=0 loops=1)
         Index Cond: ((vulnerabilities.id >= 0) AND (vulnerabilities.id <= 1000))
         Filter: (vulnerabilities.confidence = 0)
         Rows Removed by Filter: 0
         Buffers: shared hit=2

https://explain.depesz.com/s/K0U

Runtime estimates:

  • Temporary index was created in 1.241 s
  • There are 87_602 records to be updated on Gitlab.com
  • (87_602 / 1000) = 87 iterations
  • each iteration will take then approx ( 0.386 ms )
  • total execution without interval is ( 0.386 ms) * 288 = 111.168 ms
  • with 2 min interval its = ((2+0.111168) * 87 ) / 60 = ~3 hours

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Edited by 🤖 GitLab Bot 🤖

Merge request reports