Refactor `MarkResolvedAsDetected` ingestion task (!141093) · Merge requests · GitLab.org / GitLab

Mehmet Emin INAC requested to merge minac_429730_improve_statistics_ingestion into master Jan 05, 2024
What does this MR do and why?

This commit removes the CodeReuse/ActiveRecord cop disable comments from the task class by using the existing scopes and changes the logic to create the state transition records in batch instead of creating them one by one. Also, by this change, we add a guard clause to skip running the task logic if there is no redetected vulnerability.
Database review

This MR changes the way we create the vulnerability_state_transitions records from a single record at a time to all records at once.
Here is the bulk insert query
INSERT INTO "vulnerability_state_transitions" ("vulnerability_id", "to_state", "from_state", "created_at", "updated_at", "author_id", "comment", "dismissal_reason", "state_changed_at_pipeline_id")
    VALUES (18891225, 1, 3, '2024-01-05 13:21:06.572325', '2024-01-05 13:21:06.572329', NULL, NULL, NULL, NULL),
    (18891226, 1, 3, '2024-01-05 13:21:06.586022', '2024-01-05 13:21:06.586024', NULL, NULL, NULL, NULL),
    (18891227, 1, 3, '2024-01-05 13:21:06.586068', '2024-01-05 13:21:06.586069', NULL, NULL, NULL, NULL),
    (18891228, 1, 3, '2024-01-05 13:21:06.586093', '2024-01-05 13:21:06.586094', NULL, NULL, NULL, NULL),
    (18891229, 1, 3, '2024-01-05 13:21:06.586115', '2024-01-05 13:21:06.586115', NULL, NULL, NULL, NULL),
    (18891230, 1, 3, '2024-01-05 13:21:06.586137', '2024-01-05 13:21:06.586137', NULL, NULL, NULL, NULL),
    (18891231, 1, 3, '2024-01-05 13:21:06.586158', '2024-01-05 13:21:06.586158', NULL, NULL, NULL, NULL),
    (18891232, 1, 3, '2024-01-05 13:21:06.586178', '2024-01-05 13:21:06.586178', NULL, NULL, NULL, NULL),
    (18891233, 1, 3, '2024-01-05 13:21:06.586198', '2024-01-05 13:21:06.586204', NULL, NULL, NULL, NULL),
    (18891234, 1, 3, '2024-01-05 13:21:06.586224', '2024-01-05 13:21:06.586224', NULL, NULL, NULL, NULL),
    (18891235, 1, 3, '2024-01-05 13:21:06.586244', '2024-01-05 13:21:06.586244', NULL, NULL, NULL, NULL),
    (18891236, 1, 3, '2024-01-05 13:21:06.586264', '2024-01-05 13:21:06.586264', NULL, NULL, NULL, NULL),
    (18891237, 1, 3, '2024-01-05 13:21:06.586284', '2024-01-05 13:21:06.586284', NULL, NULL, NULL, NULL),
    (18891238, 1, 3, '2024-01-05 13:21:06.586304', '2024-01-05 13:21:06.586305', NULL, NULL, NULL, NULL),
    (18891239, 1, 3, '2024-01-05 13:21:06.586324', '2024-01-05 13:21:06.586325', NULL, NULL, NULL, NULL),
    (18891240, 1, 3, '2024-01-05 13:21:06.586359', '2024-01-05 13:21:06.586359', NULL, NULL, NULL, NULL),
    (18891241, 1, 3, '2024-01-05 13:21:06.586384', '2024-01-05 13:21:06.586384', NULL, NULL, NULL, NULL),
    (18891242, 1, 3, '2024-01-05 13:21:06.586407', '2024-01-05 13:21:06.586407', NULL, NULL, NULL, NULL),
    (18891243, 1, 3, '2024-01-05 13:21:06.586436', '2024-01-05 13:21:06.586436', NULL, NULL, NULL, NULL),
    (18891244, 1, 3, '2024-01-05 13:21:06.586459', '2024-01-05 13:21:06.586459', NULL, NULL, NULL, NULL),
    (18891245, 1, 3, '2024-01-05 13:21:06.586479', '2024-01-05 13:21:06.586480', NULL, NULL, NULL, NULL),
    (18891246, 1, 3, '2024-01-05 13:21:06.586506', '2024-01-05 13:21:06.586506', NULL, NULL, NULL, NULL),
    (18891247, 1, 3, '2024-01-05 13:21:06.586544', '2024-01-05 13:21:06.586545', NULL, NULL, NULL, NULL),
    (18891248, 1, 3, '2024-01-05 13:21:06.586567', '2024-01-05 13:21:06.586576', NULL, NULL, NULL, NULL),
    (18891249, 1, 3, '2024-01-05 13:21:06.586597', '2024-01-05 13:21:06.586599', NULL, NULL, NULL, NULL),
    (18891250, 1, 3, '2024-01-05 13:21:06.586619', '2024-01-05 13:21:06.586619', NULL, NULL, NULL, NULL),
    (18891251, 1, 3, '2024-01-05 13:21:06.586642', '2024-01-05 13:21:06.586642', NULL, NULL, NULL, NULL),
    (18891252, 1, 3, '2024-01-05 13:21:06.586664', '2024-01-05 13:21:06.586664', NULL, NULL, NULL, NULL),
    (18891253, 1, 3, '2024-01-05 13:21:06.586688', '2024-01-05 13:21:06.586688', NULL, NULL, NULL, NULL),
    (19214230, 1, 3, '2024-01-05 13:21:06.586712', '2024-01-05 13:21:06.586713', NULL, NULL, NULL, NULL),
    (19219189, 1, 3, '2024-01-05 13:21:06.586733', '2024-01-05 13:21:06.586734', NULL, NULL, NULL, NULL),
    (19219190, 1, 3, '2024-01-05 13:21:06.586753', '2024-01-05 13:21:06.586753', NULL, NULL, NULL, NULL),
    (20448722, 1, 3, '2024-01-05 13:21:06.586772', '2024-01-05 13:21:06.586773', NULL, NULL, NULL, NULL),
    (20857375, 1, 3, '2024-01-05 13:21:06.586806', '2024-01-05 13:21:06.586806', NULL, NULL, NULL, NULL),
    (21491479, 1, 3, '2024-01-05 13:21:06.586830', '2024-01-05 13:21:06.586836', NULL, NULL, NULL, NULL),
    (21491480, 1, 3, '2024-01-05 13:21:06.586856', '2024-01-05 13:21:06.586857', NULL, NULL, NULL, NULL),
    (22542616, 1, 3, '2024-01-05 13:21:06.586876', '2024-01-05 13:21:06.586877', NULL, NULL, NULL, NULL),
    (35388194, 1, 3, '2024-01-05 13:21:06.586897', '2024-01-05 13:21:06.586897', NULL, NULL, NULL, NULL),
    (37684798, 1, 3, '2024-01-05 13:21:06.586916', '2024-01-05 13:21:06.586917', NULL, NULL, NULL, NULL),
    (37684799, 1, 3, '2024-01-05 13:21:06.586937', '2024-01-05 13:21:06.586937', NULL, NULL, NULL, NULL),
    (37684800, 1, 3, '2024-01-05 13:21:06.586957', '2024-01-05 13:21:06.586958', NULL, NULL, NULL, NULL),
    (38226949, 1, 3, '2024-01-05 13:21:06.586977', '2024-01-05 13:21:06.586978', NULL, NULL, NULL, NULL),
    (38226950, 1, 3, '2024-01-05 13:21:06.586998', '2024-01-05 13:21:06.586998', NULL, NULL, NULL, NULL),
    (38226951, 1, 3, '2024-01-05 13:21:06.587018', '2024-01-05 13:21:06.587019', NULL, NULL, NULL, NULL),
    (38226953, 1, 3, '2024-01-05 13:21:06.587039', '2024-01-05 13:21:06.587039', NULL, NULL, NULL, NULL),
    (38400777, 1, 3, '2024-01-05 13:21:06.587058', '2024-01-05 13:21:06.587058', NULL, NULL, NULL, NULL),
    (39689098, 1, 3, '2024-01-05 13:21:06.587078', '2024-01-05 13:21:06.587079', NULL, NULL, NULL, NULL),
    (41211016, 1, 3, '2024-01-05 13:21:06.587098', '2024-01-05 13:21:06.587098', NULL, NULL, NULL, NULL),
    (41211017, 1, 3, '2024-01-05 13:21:06.587117', '2024-01-05 13:21:06.587118', NULL, NULL, NULL, NULL),
    (44285095, 1, 3, '2024-01-05 13:21:06.587143', '2024-01-05 13:21:06.587143', NULL, NULL, NULL, NULL),
    (44753773, 1, 3, '2024-01-05 13:21:06.587164', '2024-01-05 13:21:06.587164', NULL, NULL, NULL, NULL),
    (44753774, 1, 3, '2024-01-05 13:21:06.587183', '2024-01-05 13:21:06.587183', NULL, NULL, NULL, NULL),
    (45300669, 1, 3, '2024-01-05 13:21:06.587207', '2024-01-05 13:21:06.587208', NULL, NULL, NULL, NULL),
    (45300670, 1, 3, '2024-01-05 13:21:06.587232', '2024-01-05 13:21:06.587232', NULL, NULL, NULL, NULL),
    (45923663, 1, 3, '2024-01-05 13:21:06.587252', '2024-01-05 13:21:06.587252', NULL, NULL, NULL, NULL),
    (45923664, 1, 3, '2024-01-05 13:21:06.587277', '2024-01-05 13:21:06.587277', NULL, NULL, NULL, NULL),
    (46614368, 1, 3, '2024-01-05 13:21:06.588964', '2024-01-05 13:21:06.588965', NULL, NULL, NULL, NULL),
    (46614369, 1, 3, '2024-01-05 13:21:06.588990', '2024-01-05 13:21:06.588990', NULL, NULL, NULL, NULL),
    (46614370, 1, 3, '2024-01-05 13:21:06.589011', '2024-01-05 13:21:06.589012', NULL, NULL, NULL, NULL),
    (46614371, 1, 3, '2024-01-05 13:21:06.589040', '2024-01-05 13:21:06.589041', NULL, NULL, NULL, NULL),
    (46614372, 1, 3, '2024-01-05 13:21:06.589063', '2024-01-05 13:21:06.589064', NULL, NULL, NULL, NULL),
    (47366909, 1, 3, '2024-01-05 13:21:06.589087', '2024-01-05 13:21:06.589087', NULL, NULL, NULL, NULL),
    (47366910, 1, 3, '2024-01-05 13:21:06.589108', '2024-01-05 13:21:06.589108', NULL, NULL, NULL, NULL),
    (47366911, 1, 3, '2024-01-05 13:21:06.589128', '2024-01-05 13:21:06.589128', NULL, NULL, NULL, NULL),
    (47808244, 1, 3, '2024-01-05 13:21:06.589148', '2024-01-05 13:21:06.589148', NULL, NULL, NULL, NULL),
    (47808245, 1, 3, '2024-01-05 13:21:06.589168', '2024-01-05 13:21:06.589168', NULL, NULL, NULL, NULL),
    (47808246, 1, 3, '2024-01-05 13:21:06.589188', '2024-01-05 13:21:06.589188', NULL, NULL, NULL, NULL),
    (47808247, 1, 3, '2024-01-05 13:21:06.589209', '2024-01-05 13:21:06.589209', NULL, NULL, NULL, NULL),
    (47808248, 1, 3, '2024-01-05 13:21:06.589228', '2024-01-05 13:21:06.589228', NULL, NULL, NULL, NULL),
    (47808249, 1, 3, '2024-01-05 13:21:06.589249', '2024-01-05 13:21:06.589249', NULL, NULL, NULL, NULL),
    (47808250, 1, 3, '2024-01-05 13:21:06.589269', '2024-01-05 13:21:06.589269', NULL, NULL, NULL, NULL),
    (48266313, 1, 3, '2024-01-05 13:21:06.589289', '2024-01-05 13:21:06.589289', NULL, NULL, NULL, NULL),
    (50603877, 1, 3, '2024-01-05 13:21:06.589308', '2024-01-05 13:21:06.589309', NULL, NULL, NULL, NULL),
    (50603878, 1, 3, '2024-01-05 13:21:06.589328', '2024-01-05 13:21:06.589329', NULL, NULL, NULL, NULL),
    (50983758, 1, 3, '2024-01-05 13:21:06.589355', '2024-01-05 13:21:06.589356', NULL, NULL, NULL, NULL),
    (15782738, 1, 3, '2024-01-05 13:21:06.589375', '2024-01-05 13:21:06.589376', NULL, NULL, NULL, NULL),
    (18889624, 1, 3, '2024-01-05 13:21:06.589396', '2024-01-05 13:21:06.589396', NULL, NULL, NULL, NULL),
    (18889625, 1, 3, '2024-01-05 13:21:06.589417', '2024-01-05 13:21:06.589417', NULL, NULL, NULL, NULL),
    (18889626, 1, 3, '2024-01-05 13:21:06.589437', '2024-01-05 13:21:06.589437', NULL, NULL, NULL, NULL),
    (18889627, 1, 3, '2024-01-05 13:21:06.589456', '2024-01-05 13:21:06.589457', NULL, NULL, NULL, NULL),
    (18889628, 1, 3, '2024-01-05 13:21:06.589476', '2024-01-05 13:21:06.589476', NULL, NULL, NULL, NULL),
    (18889629, 1, 3, '2024-01-05 13:21:06.589497', '2024-01-05 13:21:06.589497', NULL, NULL, NULL, NULL),
    (18889630, 1, 3, '2024-01-05 13:21:06.589520', '2024-01-05 13:21:06.589520', NULL, NULL, NULL, NULL),
    (18889631, 1, 3, '2024-01-05 13:21:06.589539', '2024-01-05 13:21:06.589539', NULL, NULL, NULL, NULL),
    (18889632, 1, 3, '2024-01-05 13:21:06.589559', '2024-01-05 13:21:06.589559', NULL, NULL, NULL, NULL),
    (18889633, 1, 3, '2024-01-05 13:21:06.589578', '2024-01-05 13:21:06.589578', NULL, NULL, NULL, NULL),
    (18889634, 1, 3, '2024-01-05 13:21:06.589598', '2024-01-05 13:21:06.589598', NULL, NULL, NULL, NULL),
    (18889635, 1, 3, '2024-01-05 13:21:06.589617', '2024-01-05 13:21:06.589617', NULL, NULL, NULL, NULL),
    (18889636, 1, 3, '2024-01-05 13:21:06.589636', '2024-01-05 13:21:06.589636', NULL, NULL, NULL, NULL),
    (18889637, 1, 3, '2024-01-05 13:21:06.589660', '2024-01-05 13:21:06.589660', NULL, NULL, NULL, NULL),
    (18889638, 1, 3, '2024-01-05 13:21:06.589679', '2024-01-05 13:21:06.589680', NULL, NULL, NULL, NULL),
    (18889639, 1, 3, '2024-01-05 13:21:06.589699', '2024-01-05 13:21:06.589699', NULL, NULL, NULL, NULL),
    (18889640, 1, 3, '2024-01-05 13:21:06.589718', '2024-01-05 13:21:06.589719', NULL, NULL, NULL, NULL),
    (18889641, 1, 3, '2024-01-05 13:21:06.589738', '2024-01-05 13:21:06.589738', NULL, NULL, NULL, NULL),
    (18889642, 1, 3, '2024-01-05 13:21:06.589758', '2024-01-05 13:21:06.589758', NULL, NULL, NULL, NULL),
    (18889643, 1, 3, '2024-01-05 13:21:06.589777', '2024-01-05 13:21:06.589778', NULL, NULL, NULL, NULL),
    (18889644, 1, 3, '2024-01-05 13:21:06.589797', '2024-01-05 13:21:06.589797', NULL, NULL, NULL, NULL),
    (18889645, 1, 3, '2024-01-05 13:21:06.589819', '2024-01-05 13:21:06.589819', NULL, NULL, NULL, NULL),
    (18889646, 1, 3, '2024-01-05 13:21:06.589847', '2024-01-05 13:21:06.589848', NULL, NULL, NULL, NULL),
    (18889647, 1, 3, '2024-01-05 13:21:06.589867', '2024-01-05 13:21:06.589867', NULL, NULL, NULL, NULL)
Here is the execution plan: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24662/commands/79706
Edited Jan 05, 2024 by Mehmet Emin INAC
Refactor `MarkResolvedAsDetected` ingestion task

What does this MR do and why?

Database review

Merge request reports
