Skip to content

Replace undefined severity with unknown severity for occurrences

Can Eldem requested to merge replace-undefined-with-unkown into master

What does this MR do?

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

Application logic changed in this MR

!25884 (merged)

This Mr updates existing records

Query analysis

SELECT Count(*) 
FROM   vulnerability_occurrences 
WHERE  severity = 0

select

Aggregate  (cost=1047.41..1047.42 rows=1 width=8) (actual time=8.542..8.542 rows=1 loops=1)
   Buffers: shared hit=3288 read=67
   I/O Timings: read=1.480
   ->  Index Only Scan using undefined_vulnerabilities on public.vulnerability_occurrences  (cost=0.29..987.61 rows=23921 width=0) (actual time=0.179..6.241 rows=23964 loops=1)
         Heap Fetches: 702
         Buffers: shared hit=3288 read=67
         I/O Timings: read=1.480

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

update

UPDATE "vulnerability_occurrences" 
SET    "severity" = 2 
WHERE  "vulnerability_occurrences"."severity" = 0 
       AND "vulnerability_occurrences"."id" BETWEEN 0 AND 1000 
ModifyTable on public.vulnerability_occurrences  (cost=0.29..11.94 rows=9 width=1092) (actual time=481.218..481.219 rows=0 loops=1)
   Buffers: shared hit=4812 read=456 dirtied=338
   I/O Timings: read=453.775
   ->  Index Scan using undefined_vulnerabilities on public.vulnerability_occurrences  (cost=0.29..11.94 rows=9 width=1092) (actual time=0.263..6.553 rows=183 loops=1)
         Index Cond: ((vulnerability_occurrences.id >= 0) AND (vulnerability_occurrences.id <= 1000))
         Filter: (vulnerability_occurrences.severity = 0)
         Rows Removed by Filter: 0
         Buffers: shared hit=64 read=69
         I/O Timings: read=5.463

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

Runtime estimates:

  • There are 23_044 records to be updated on Gitlab.com
  • (23_044 / 1000) = 24 iterations
  • each iteration will take then approx ( half second )
  • total execution without interval is 12 seconds
  • with 5 min interval its 1 hour

Conformity

Edited by 🤖 GitLab Bot 🤖

Merge request reports