Skip to content

Replace undefined confidence with unknown severity for occurrences

Can Eldem requested to merge remove-undefined-from-confidence 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

gdk-ee/gitlab [remove-undefined-from-confidence●] » rails db:migrate VERSION=20200506085748
== 20200506085748 UpdateUndefinedConfidenceFromOccurrences: migrating =========
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerability_occurrences, :id, {:where=>"confidence = 0", :name=>"undefined_vulnerabilities", :algorithm=>:concurrently})
   -> 0.0034s
== 20200506085748 UpdateUndefinedConfidenceFromOccurrences: migrated (0.0356s)

DOWN

== 20200506085748 UpdateUndefinedConfidenceFromOccurrences: reverting =========
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerability_occurrences, :id, {:where=>"confidence = 0", :name=>"undefined_vulnerabilities", :algorithm=>:concurrently})
   -> 0.0031s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- remove_index(:vulnerability_occurrences, {:where=>"confidence = 0", :name=>"undefined_vulnerabilities", :algorithm=>:concurrently, :column=>:id})
   -> 0.0046s
-- execute("RESET ALL")
   -> 0.0002s
== 20200506085748 UpdateUndefinedConfidenceFromOccurrences: reverted (0.0082s)

Query

explain SELECT Count(id) FROM vulnerability_occurrences WHERE confidence = 0
Aggregate  (cost=10252.36..10252.37 rows=1 width=8) (actual time=89.844..89.844 rows=1 loops=1)
   Buffers: shared hit=12219 read=917
   I/O Timings: read=21.162
   ->  Index Only Scan using undefined_vulnerabilities on public.vulnerability_occurrences  (cost=0.42..9534.60 rows=287103 width=8) (actual time=0.255..61.929 rows=288256 loops=1)
         Heap Fetches: 368
         Buffers: shared hit=12219 read=917
         I/O Timings: read=21.162

https://explain.depesz.com/s/9bWJ

explain UPDATE "vulnerability_occurrences" SET  "confidence" = 2 WHERE "vulnerability_occurrences"."confidence" = 0 AND "vulnerability_occurrences"."id" BETWEEN 0 AND 1000
ModifyTable on public.vulnerability_occurrences  (cost=0.42..83.26 rows=86 width=1099) (actual time=0.024..0.024 rows=0 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using undefined_vulnerabilities on public.vulnerability_occurrences  (cost=0.42..83.26 rows=86 width=1099) (actual time=0.022..0.022 rows=0 loops=1)
         Index Cond: ((vulnerability_occurrences.id >= 0) AND (vulnerability_occurrences.id <= 1000))
         Filter: (vulnerability_occurrences.confidence = 0)
         Rows Removed by Filter: 0
         Buffers: shared hit=4

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

Runtime estimates:

  • Temporary index was created in 47 seconds
  • There are 288_256 records to be updated on Gitlab.com
  • (288_256 / 1000) = 288 iterations
  • each iteration will take then approx ( 0.298 ms )
  • total execution without interval is (0.298ms) * 288 = 85.824 ms
  • with 2 min interval its = ((2+0.085824) * 288 ) / 60 = ~10 hours

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Edited by 🤖 GitLab Bot 🤖

Merge request reports