Populate `has_vulnerabilities` values
What does this MR do?
We've recently introduced a new column called has_vulnerabilities
for the project_settings
table and this MR populates the values for that column.
Related to #244380 (closed)
Database review
This MR introduces a post-deployment migration to schedule background jobs to populate values for ProjectSetting#has_vulnerabilities
.
The initial query to fetch project_id
s in batches is as follows;
For the initial ID;
SELECT "projects"."id" FROM "projects" WHERE (EXISTS (SELECT 1 FROM vulnerabilities WHERE vulnerabilities.project_id = projects.id)) ORDER BY "projects"."id" ASC LIMIT 1
Limit (cost=102.33..176.77 rows=1 width=4) (actual time=97.373..97.375 rows=1 loops=1)
Buffers: shared hit=2227 read=66 dirtied=19
I/O Timings: read=94.305
-> Merge Semi Join (cost=102.33..520046.39 rows=6985 width=4) (actual time=97.371..97.373 rows=1 loops=1)
Merge Cond: (projects.id = vulnerabilities.project_id)
Buffers: shared hit=2227 read=66 dirtied=19
I/O Timings: read=94.305
-> Index Only Scan using projects_pkey on public.projects (cost=0.43..323205.06 rows=15603285 width=4) (actual time=1.290..96.856 rows=4133 loops=1)
Heap Fetches: 46
Buffers: shared hit=2223 read=66 dirtied=19
I/O Timings: read=94.305
-> Index Only Scan using index_vulnerabilities_on_project_id on public.vulnerabilities (cost=0.43..105584.53 rows=4181157 width=8) (actual time=0.040..0.040 rows=1 loops=1)
Heap Fetches: 0
Buffers: shared hit=4
For the upperbound;
SELECT "projects"."id" FROM "projects" WHERE (EXISTS (SELECT 1 FROM vulnerabilities WHERE vulnerabilities.project_id = projects.id)) AND "projects"."id" >= 1 ORDER BY "projects"."id" ASC LIMIT 1 OFFSET 1000
There are currently 9100 projects to be migrated which in total makes 10 background jobs to be enqueued.
rake db:migrate
== 20201103192526 SchedulePopulateHasVulnerabilities: migrating ===============
== 20201103192526 SchedulePopulateHasVulnerabilities: migrated (0.0582s) ======
rake db:migrate:down
== 20201103192526 SchedulePopulateHasVulnerabilities: reverting ===============
== 20201103192526 SchedulePopulateHasVulnerabilities: reverted (0.0000s) ======
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry - [-] Documentation (if required)
-
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides - [-] Separation of EE specific content
Availability and Testing
- [-] Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
- [-] Tested in all supported browsers
- [-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Security
If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:
- [-] Label as security and @ mention
@gitlab-com/gl-security/appsec
- [-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
- [-] Security reports checked/validated by a reviewer from the AppSec team
Edited by Mehmet Emin INAC