Skip to content

Add migration to resolve vulnerabilities from removed analyzers

Adam Cohen requested to merge 444926-resolve-findings-from-removed-sast-analyzers into master

What does this MR do and why?

This MR resolves vulnerabilities for the following Category:SAST analyzers that have been removed in GitLab %15.4, %16.0, and %17.0:

Removed in Project Scanner ID Number of vulnerabilities Number of vulnerabilities where state: detected
%15.4 eslint eslint 13,118,190 8,539,129
gosec gosec 202,172 164,063
bandit bandit 769,148 592,668
%16.0 security-code-scan security_code_scan 10,808 6,496
%17.0 Brakeman brakeman 61,694 58,129
flawfinder flawfinder 3,520,412 2,714,036
MobSF mobsf 84,370 65,589
PHPCS Security Audit phpcs_security_audit 2,926,743 2,496,363
NodeJS Scan njsscan 208,066 172,886
nodejs-scan 123,101 89,886
nodejs_scan 14,189 12,577
 
  Totals: 21,038,893 14,911,822

Since we need to resolve all the vulnerabilities that belong to the above removed analyzers and have state: detected, that means the total number of vulnerabilities to resolve is 14,911,822.

The following are details about all vulnerabilities, for informational purposes:

Total Vulnerabilities where state: detected 75,103,676
Total Vulnerabilities where state: detected and archived: false 69,092,451
Total Vulnerabilities: 101,440,370

Vulnerability resolution process

Vulnerabilities are normally resolved using Vulnerabilities::ResolveService, which does the following:

  1. Inserts a new vulnerability_state_transitions record, containing the current state and the new resolved state.

  2. Updates the vulnerabilities record and sets the state to resolved.

    There's a trigger on the vulnerabilities table which automatically updates the vulnerability_reads table after the vulnerabilities table is updated, so we don't need to explicitly add an SQL call to do this for us.

  3. Updates the vulnerabilities_reads table and sets the dismissal_reason to null.

  4. Inserts a new record into the notes table with a comment explaining why the vulnerability was resolved.

  5. Inserts a new system_note_metadata record indicating that the vulnerability was resolved.

  6. Deletes records from the vulnerability_user_mentions table matching the vulnerability.id and the note.id for the note record created in step 4 above.

    We don't implement this operation in this MR, since it's not possible for an existing vulnerability_user_mentions record to match the note.id we just created. (TODO: Apply this same optimization to Vulnerabilities::ResolveService so that we can save a query - need to create an issue for this).

  7. Updates the vulnerability_statistics entry for the project_id related to the vulnerability being resolved.

  8. Deletes the dismissed vulnerability_feedback records belonging to the removed analyzers.

So every time a vulnerability is resolved, there are 8 SQL statements executed, 9 if we count the trigger action for updating the vulnerability_reads table.

This batched background migration loops through the vulnerability_reads records where state: detected, and attempts to resolve each vulnerability if it was created by one of the analyzers that have been removed.

Migration estimates

Option Description Official Estimate Manual Estimate
(Avg batch time * Num Batches)
1
  • bulk updates
  • SQL batch filtering by joining with the vulnerability_scanners table
  • trying to use the idx_vulnerability_reads_for_traversal_ids_queries_srt_severity index on the vulnerability_reads table by passing archived: false
4 months, 2 days 1055.43s * 7510
= 3 months, 9 hours
2 Same as 1 above, but trying to use a new tmp_index_vulnerability_scanners_on_external_id index 3 months, 1 week 885.11s * 7510
= 2 months, 2 weeks
3 Abandoning the bulk updates and SQL batch filtering of option 1, and instead using ruby to filter vulnerabilities matching the removed analyzers 1 month, 1 week 367.65s * 7510
= 1 month, 1 day
4 Slow iteration combined with a CTE, implemented in use CTE 3 months, 1 week 831.16s * 7510
= 2 months, 1 week
5 Same as 4 above, but with the addition of an index on vulnerability_reads (id) where state = 1 and adding a batching_scope on vulnerability_reads.state = 1 3 months, 4 days 809.58s * 7510
= 2 months, 1 week
6 Same as 5, but increased SUB_BATCH_SIZE from 100 to 1000 and switched from batching_scope to scope_to 9 months, 4 weeks 2552.82s * 7510
= 7 months, 1 week
7 Same as 6, but Switched SUB_BATCH_SIZE back to 100 10 months, 3 days 2590.17s * 7510
= 7 months, 1 week
8 Same as 5 2 months, 2 weeks 632.36s * 7510
= 1 month, 3 weeks
9 Same as 3, but using SUB_BATCH_SIZE: 500 2 months, 1 week 604.14s * 7510
= 1 month, 3 weeks
10 Using Hardcoded batch min and max values 2 years, 1 week  
11 Baseline migration: Use scope_to relation.where(state: 1) and don't perform any operations on vulnerabilities. Commit: use scope_to with no-op in a txn 2 weeks, 9 hours, 36 minutes [11.17s, 120s].max * 7510
= 1 week, 3 days
12 Same as 11 but after adding back code to perform bulk write to resolve vulnerabilities 2 weeks, 6 hours, 54 minutes [42.97s, 120s].max * 7510
= 1 week, 3 days
13 Same as 12 but after adding back code to perform bulk write to remove vulnerability dismissal reason 2 weeks, 6 hours, 54 minutes [46.44s, 120s].max * 7510
= 1 week, 3 days
14 Same as 13 but after adding back code to perform bulk creation of state transitions 2 weeks, 8 hours [45.99s, 120s].max * 7510
= 1 week, 3 days
15 Same as 14 but after adding code to perform bulk creation of system notes with metadata 2 weeks, 10 hours, and 58 minutes [62.46s, 120s].max * 7510
= 1 week, 3 days
16 Same as 15 but after adding code to Use bulk operation for vulnerability statistics. All operations have now been implemented as bulk queries 2 weeks, 9 hours, and 36 minutes [60.98s, 120s].max * 7510
= 1 week, 3 days
17 Same as 16 but after adding code to Use transaction in migration. 2 weeks, 11 hours, and 10 minutes [76.15s, 120s].max * 7510
= 1 week, 3 days
18 Same as 17 but after adding code to Use CTE with limit suggested here. 2 weeks, 11 hours, and 10 minutes [76.64s, 120s].max * 7510
= 1 week, 3 days
19 Same as 18 but after adding code to Add bulk_destroy_dismissal_feedback function as discussed here 2 weeks, 12 hours, and 22 minutes [69.14s, 120s].max * 7510
= 1 week, 3 days
20 Same as 19 but after removing the hardcoded batch min and max values added in option 10 2 weeks, 12 hours, and 22 minutes [91.45s, 120s].max * 7510
= 1 week, 3 days
21 Same as 20 but using Time.current instead of now() 2 weeks, 12 hours, and 4 minutes [101.36s, 120s].max * 7510
= 1 week, 3 days

Query plans

  1. Insert a new vulnerability_state_transitions record using the bulk_create_state_transitions function:

    Query 
    INSERT INTO "vulnerability_state_transitions"
  ("vulnerability_id", "to_state", "from_state", "created_at", "updated_at", "author_id", "comment")
  VALUES
   (37044017, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044018, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044019, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044020, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044021, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044022, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044023, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044024, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044025, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044026, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044027, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044028, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044029, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044030, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044031, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044032, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044033, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044034, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044035, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044036, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044037, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044038, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044039, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044040, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044041, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044042, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044043, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044044, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044045, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044046, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044047, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044048, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044049, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044050, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044051, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044052, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044053, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044054, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044055, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044056, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044057, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044058, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044059, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044060, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044061, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044062, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044063, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044064, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044065, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044066, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044067, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044068, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044069, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044070, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044071, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044072, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044073, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044074, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044075, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044076, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044077, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044078, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044079, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044080, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044081, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044082, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044083, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044084, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044085, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044086, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044087, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044088, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044089, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044090, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044091, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044092, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044093, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044094, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044095, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044096, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044097, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044098, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044099, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044100, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044101, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044102, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044103, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044104, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044105, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044106, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044107, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044108, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044109, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044110, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044111, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044112, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044113, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044114, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044115, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT'),
   (37044116, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT');

    query plan

    Time: 328.629 ms  
  - planning: 0.389 ms  
  - execution: 328.240 ms  
    - I/O read: 125.713 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 2442 (~19.10 MiB) from the buffer pool  
  - reads: 131 (~1.00 MiB) from the OS file cache, including disk I/O  
  - dirtied: 23 (~184.00 KiB)  
  - writes: 4 (~32.00 KiB)

  2. Update the vulnerabilities record and set the state to resolved, using the bulk_resolve function:

    Query 
    UPDATE vulnerabilities SET state = 3, resolved_by_id = 208, resolved_at = now() 
   WHERE vulnerabilities.id in (
     37044017,
     37044018,
     37044019,
     37044020,
     37044021,
     37044022,
     37044023,
     37044024,
     37044025,
     37044026,
     37044027,
     37044028,
     37044029,
     37044030,
     37044031,
     37044032,
     37044033,
     37044034,
     37044035,
     37044036,
     37044037,
     37044038,
     37044039,
     37044040,
     37044041,
     37044042,
     37044043,
     37044044,
     37044045,
     37044046,
     37044047,
     37044048,
     37044049,
     37044050,
     37044051,
     37044052,
     37044053,
     37044054,
     37044055,
     37044056,
     37044057,
     37044058,
     37044059,
     37044060,
     37044061,
     37044062,
     37044063,
     37044064,
     37044065,
     37044066,
     37044067,
     37044068,
     37044069,
     37044070,
     37044071,
     37044072,
     37044073,
     37044074,
     37044075,
     37044076,
     37044077,
     37044078,
     37044079,
     37044080,
     37044081,
     37044082,
     37044083,
     37044084,
     37044085,
     37044086,
     37044087,
     37044088,
     37044089,
     37044090,
     37044091,
     37044092,
     37044093,
     37044094,
     37044095,
     37044096,
     37044097,
     37044098,
     37044099,
     37044100,
     37044101,
     37044102,
     37044103,
     37044104,
     37044105,
     37044106,
     37044107,
     37044108,
     37044109,
     37044110,
     37044111,
     37044112,
     37044113,
     37044114,
     37044115,
     37044116
   );

    query plan

    Time: 1.027 s  
  - planning: 1.977 ms  
  - execution: 1.025 s  
    - I/O read: 132.316 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 6657 (~52.00 MiB) from the buffer pool  
  - reads: 129 (~1.00 MiB) from the OS file cache, including disk I/O  
  - dirtied: 76 (~608.00 KiB)  
  - writes: 7 (~56.00 KiB)

  3. Update the vulnerabilities_reads table and set the dismissal_reason to null, using the bulk_remove_dismissal_reason function:

    Query 
    UPDATE vulnerability_reads SET dismissal_reason = NULL
   WHERE vulnerability_reads.vulnerability_id in (
     37044017,
     37044018,
     37044019,
     37044020,
     37044021,
     37044022,
     37044023,
     37044024,
     37044025,
     37044026,
     37044027,
     37044028,
     37044029,
     37044030,
     37044031,
     37044032,
     37044033,
     37044034,
     37044035,
     37044036,
     37044037,
     37044038,
     37044039,
     37044040,
     37044041,
     37044042,
     37044043,
     37044044,
     37044045,
     37044046,
     37044047,
     37044048,
     37044049,
     37044050,
     37044051,
     37044052,
     37044053,
     37044054,
     37044055,
     37044056,
     37044057,
     37044058,
     37044059,
     37044060,
     37044061,
     37044062,
     37044063,
     37044064,
     37044065,
     37044066,
     37044067,
     37044068,
     37044069,
     37044070,
     37044071,
     37044072,
     37044073,
     37044074,
     37044075,
     37044076,
     37044077,
     37044078,
     37044079,
     37044080,
     37044081,
     37044082,
     37044083,
     37044084,
     37044085,
     37044086,
     37044087,
     37044088,
     37044089,
     37044090,
     37044091,
     37044092,
     37044093,
     37044094,
     37044095,
     37044096,
     37044097,
     37044098,
     37044099,
     37044100,
     37044101,
     37044102,
     37044103,
     37044104,
     37044105,
     37044106,
     37044107,
     37044108,
     37044109,
     37044110,
     37044111,
     37044112,
     37044113,
     37044114,
     37044115,
     37044116
   );

    query plan

    Time: 417.218 ms  
  - planning: 3.629 ms  
  - execution: 413.589 ms  
    - I/O read: 376.501 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 9234 (~72.10 MiB) from the buffer pool  
  - reads: 587 (~4.60 MiB) from the OS file cache, including disk I/O  
  - dirtied: 122 (~976.00 KiB)  
  - writes: 11 (~88.00 KiB)

  4. Insert a new record into the notes table with a comment explaining why the vulnerability was resolved, using the bulk_create_system_note_with_metadata function:

    Query 
    INSERT INTO notes
  (note, noteable_type, author_id, created_at, updated_at, project_id, noteable_id, system, discussion_id, namespace_id)
  VALUES
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044017, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044018, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044019, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044020, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044021, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044022, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044023, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044024, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044025, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044026, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044027, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044028, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044029, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044030, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044031, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044032, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044033, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044034, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044035, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044036, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044037, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044038, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044039, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044040, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044041, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044042, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044043, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044044, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044045, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044046, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044047, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044048, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044049, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044050, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044051, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044052, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044053, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044054, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044055, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044056, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044057, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044058, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044059, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044060, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044061, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044062, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044063, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044064, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044065, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044066, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044067, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044068, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044069, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044070, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044071, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044072, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044073, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044074, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044075, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044076, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044077, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044078, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044079, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044080, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044081, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044082, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044083, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044084, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044085, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044086, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044087, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044088, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044089, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044090, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044091, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044092, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044093, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044094, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044095, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044096, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044097, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044098, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044099, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044100, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044101, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044102, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044103, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044104, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044105, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044106, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044107, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044108, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044109, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044110, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044111, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044112, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044113, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044114, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044115, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289),
  ('Vulnerability resolved comment', 'Vulnerability', 7849066, 'now()', 'now()', 31911086, 37044116, 'TRUE', 'd8e87010e7381fed0c13a373948f751968175336', 8165289)
  RETURNING id;

    query plan

    Time: 315.017 ms  
  - planning: 0.368 ms  
  - execution: 314.649 ms  
    - I/O read: 190.634 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 4366 (~34.10 MiB) from the buffer pool  
  - reads: 196 (~1.50 MiB) from the OS file cache, including disk I/O  
  - dirtied: 114 (~912.00 KiB)  
  - writes: 8 (~64.00 KiB)

  5. Insert a new record into the system_note_metadata table with a vulnerability_resolved action, using the bulk_create_system_note_with_metadata function:

    Query 
    INSERT INTO system_note_metadata (action, created_at, updated_at, note_id)
  VALUES 
  ('vulnerability_resolved', 'now()', 'now()', 1),
  ('vulnerability_resolved', 'now()', 'now()', 2),
  ('vulnerability_resolved', 'now()', 'now()', 3),
  ('vulnerability_resolved', 'now()', 'now()', 4),
  ('vulnerability_resolved', 'now()', 'now()', 5),
  ('vulnerability_resolved', 'now()', 'now()', 6),
  ('vulnerability_resolved', 'now()', 'now()', 7),
  ('vulnerability_resolved', 'now()', 'now()', 11),
  ('vulnerability_resolved', 'now()', 'now()', 16),
  ('vulnerability_resolved', 'now()', 'now()', 17),
  ('vulnerability_resolved', 'now()', 'now()', 20),
  ('vulnerability_resolved', 'now()', 'now()', 21),
  ('vulnerability_resolved', 'now()', 'now()', 22),
  ('vulnerability_resolved', 'now()', 'now()', 23),
  ('vulnerability_resolved', 'now()', 'now()', 24),
  ('vulnerability_resolved', 'now()', 'now()', 25),
  ('vulnerability_resolved', 'now()', 'now()', 28),
  ('vulnerability_resolved', 'now()', 'now()', 29),
  ('vulnerability_resolved', 'now()', 'now()', 33),
  ('vulnerability_resolved', 'now()', 'now()', 34),
  ('vulnerability_resolved', 'now()', 'now()', 35),
  ('vulnerability_resolved', 'now()', 'now()', 36),
  ('vulnerability_resolved', 'now()', 'now()', 37),
  ('vulnerability_resolved', 'now()', 'now()', 38),
  ('vulnerability_resolved', 'now()', 'now()', 39),
  ('vulnerability_resolved', 'now()', 'now()', 40),
  ('vulnerability_resolved', 'now()', 'now()', 41),
  ('vulnerability_resolved', 'now()', 'now()', 48),
  ('vulnerability_resolved', 'now()', 'now()', 49),
  ('vulnerability_resolved', 'now()', 'now()', 51),
  ('vulnerability_resolved', 'now()', 'now()', 53),
  ('vulnerability_resolved', 'now()', 'now()', 54),
  ('vulnerability_resolved', 'now()', 'now()', 55),
  ('vulnerability_resolved', 'now()', 'now()', 57),
  ('vulnerability_resolved', 'now()', 'now()', 58),
  ('vulnerability_resolved', 'now()', 'now()', 60),
  ('vulnerability_resolved', 'now()', 'now()', 63),
  ('vulnerability_resolved', 'now()', 'now()', 75),
  ('vulnerability_resolved', 'now()', 'now()', 76),
  ('vulnerability_resolved', 'now()', 'now()', 77),
  ('vulnerability_resolved', 'now()', 'now()', 78),
  ('vulnerability_resolved', 'now()', 'now()', 79),
  ('vulnerability_resolved', 'now()', 'now()', 81),
  ('vulnerability_resolved', 'now()', 'now()', 83),
  ('vulnerability_resolved', 'now()', 'now()', 84),
  ('vulnerability_resolved', 'now()', 'now()', 85),
  ('vulnerability_resolved', 'now()', 'now()', 86),
  ('vulnerability_resolved', 'now()', 'now()', 87),
  ('vulnerability_resolved', 'now()', 'now()', 88),
  ('vulnerability_resolved', 'now()', 'now()', 89),
  ('vulnerability_resolved', 'now()', 'now()', 90),
  ('vulnerability_resolved', 'now()', 'now()', 91),
  ('vulnerability_resolved', 'now()', 'now()', 92),
  ('vulnerability_resolved', 'now()', 'now()', 93),
  ('vulnerability_resolved', 'now()', 'now()', 94),
  ('vulnerability_resolved', 'now()', 'now()', 96),
  ('vulnerability_resolved', 'now()', 'now()', 99),
  ('vulnerability_resolved', 'now()', 'now()', 100),
  ('vulnerability_resolved', 'now()', 'now()', 101),
  ('vulnerability_resolved', 'now()', 'now()', 102),
  ('vulnerability_resolved', 'now()', 'now()', 103),
  ('vulnerability_resolved', 'now()', 'now()', 104),
  ('vulnerability_resolved', 'now()', 'now()', 105),
  ('vulnerability_resolved', 'now()', 'now()', 106),
  ('vulnerability_resolved', 'now()', 'now()', 108),
  ('vulnerability_resolved', 'now()', 'now()', 109),
  ('vulnerability_resolved', 'now()', 'now()', 111),
  ('vulnerability_resolved', 'now()', 'now()', 112),
  ('vulnerability_resolved', 'now()', 'now()', 113),
  ('vulnerability_resolved', 'now()', 'now()', 114),
  ('vulnerability_resolved', 'now()', 'now()', 115),
  ('vulnerability_resolved', 'now()', 'now()', 116),
  ('vulnerability_resolved', 'now()', 'now()', 117),
  ('vulnerability_resolved', 'now()', 'now()', 118),
  ('vulnerability_resolved', 'now()', 'now()', 120),
  ('vulnerability_resolved', 'now()', 'now()', 121),
  ('vulnerability_resolved', 'now()', 'now()', 122),
  ('vulnerability_resolved', 'now()', 'now()', 124),
  ('vulnerability_resolved', 'now()', 'now()', 125),
  ('vulnerability_resolved', 'now()', 'now()', 127),
  ('vulnerability_resolved', 'now()', 'now()', 128),
  ('vulnerability_resolved', 'now()', 'now()', 132),
  ('vulnerability_resolved', 'now()', 'now()', 133),
  ('vulnerability_resolved', 'now()', 'now()', 134),
  ('vulnerability_resolved', 'now()', 'now()', 135),
  ('vulnerability_resolved', 'now()', 'now()', 136),
  ('vulnerability_resolved', 'now()', 'now()', 137),
  ('vulnerability_resolved', 'now()', 'now()', 138),
  ('vulnerability_resolved', 'now()', 'now()', 139),
  ('vulnerability_resolved', 'now()', 'now()', 141),
  ('vulnerability_resolved', 'now()', 'now()', 142),
  ('vulnerability_resolved', 'now()', 'now()', 143),
  ('vulnerability_resolved', 'now()', 'now()', 144),
  ('vulnerability_resolved', 'now()', 'now()', 145),
  ('vulnerability_resolved', 'now()', 'now()', 146),
  ('vulnerability_resolved', 'now()', 'now()', 147),
  ('vulnerability_resolved', 'now()', 'now()', 148),
  ('vulnerability_resolved', 'now()', 'now()', 150),
  ('vulnerability_resolved', 'now()', 'now()', 151),
  ('vulnerability_resolved', 'now()', 'now()', 152);

    query plan

    Time: 68.312 ms  
  - planning: 0.192 ms  
  - execution: 68.120 ms  
    - I/O read: 1.539 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 802 (~6.30 MiB) from the buffer pool  
  - reads: 9 (~72.00 KiB) from the OS file cache, including disk I/O  
  - dirtied: 11 (~88.00 KiB)  
  - writes: 1 (~8.00 KiB)

  6. Update the vulnerability_statistics entry for the project_id related to the vulnerability being resolved, using the bulk_update_vulnerability_statistics function:

    • Best case scenario, all severity count changes belong to the same project_id:

      Query 
      UPDATE vulnerability_statistics
SET    "critical" = GREATEST("critical" - 93, 0), "high" = GREATEST("high" - 5, 0), "medium" = GREATEST("medium" - 2, 0), "total" = GREATEST("total" - 100, 0), letter_grade = (CASE
  WHEN critical - 93 > 0 THEN
    4
  WHEN high + unknown - (5 + 0) > 0 THEN
    3
  WHEN medium - 2 > 0 THEN
    2
  WHEN low - 0 > 0 THEN
    1
  ELSE
    0
END
), updated_at = now()
WHERE  project_id = 31911086

      query plan

      Time: 8.045 ms  
  - planning: 1.002 ms  
  - execution: 7.043 ms  
    - I/O read: 6.379 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 12 (~96.00 KiB) from the buffer pool  
  - reads: 6 (~48.00 KiB) from the OS file cache, including disk I/O  
  - dirtied: 2 (~16.00 KiB)  
  - writes: 0

    • Worst case scenario, all severity count changes belong to different project_id values

      Same result as above, but multiplied by QueueResolveVulnerabilitiesForRemovedAnalyzers::SUB_BATCH_SIZE.

      For the current value of 100, this would be 100 * 8.045ms = 804.5ms = 0.8045 seconds

  7. Delete the dismissed vulnerability_feedback records belonging to the removed analyzers, using the bulk_destroy_dismissal_feedback function:

    Query 
    DELETE FROM vulnerability_feedback
WHERE feedback_type = 0
AND finding_uuid IN (
  'b336fc3c-41bd-5a6d-b364-d7ca4e26caf7',
  'b2ccf96d-17fc-526b-bf4c-a2bf589f8436',
  'b281314c-689d-51fc-8464-85a2355170b7',
  'b27e8f3b-dab1-5e51-a05e-00ffce87fbf9',
  'b27bd76d-aae3-5e34-9d9f-7c2d595c5857',
  'b27309d6-9626-5bc7-bd60-5312724d9c34',
  'b21cc389-aed4-5360-95ff-280d5a953e57',
  'b1e0340a-70cb-52a5-91fb-d87dc0d32004',
  'b1df20f1-11cb-578f-8e64-8b9f845881c2',
  'b1cc579e-e8e6-5d47-88d0-514d3a6c8350',
  'b133bb9d-ecf5-5324-8a7f-3f07bb63dd08',
  'b109b04c-8427-5b42-9ce0-3622ed785430',
  'b09bf3a1-bdb3-5b00-aa8e-df3df83f8375',
  'b061b16c-117f-5857-8c47-ca6202824058',
  'b003ba97-8b12-551c-97a9-397fdf2b6273',
  'afdd96f3-b94a-5d17-8be1-9c86bd049218',
  'afdaa4af-5bfa-5374-8b0f-86f22979097e',
  'afcd136e-8c2f-5a2d-a7a0-1e7fe32b68a4',
  'af9ea6c1-f14d-5e53-be08-46588b62fa47',
  'af7aa09d-dab0-5f4d-994a-03a8da987b1f',
  'af45460e-918c-57a4-9e0f-f33ab7eb49dd',
  'ae93b89c-38e9-5158-911a-ea22c5c10e6a',
  'ae7a7ed4-1b50-5454-abe1-9780347e5005',
  'ae65f00d-a0d6-55fc-97f6-ff1e51c1c6f0',
  'ae35be41-968b-533d-806b-f354dcc61334',
  'ae02390b-7612-5508-b2e8-d538aee88d64',
  'adfd7a99-bc8c-55bb-bcfa-1d59806123bb',
  'ad884c4b-05ef-578f-89b7-ff9b5b149ac3',
  'ad3c1529-a525-5b85-a466-28612caa2fe1',
  'ad00e7e3-120c-5306-b195-dd0e5aa31c88',
  'acac86f9-1655-5bf6-91cf-ac291ec8f2a8',
  'aca75650-1bff-5faf-b94c-7b6dc7e159e2',
  'ac9cbbac-748a-5669-8e4b-0a6d9687d793',
  'ac784905-5606-5222-8e22-faf5a42ccd3a',
  'ac40607f-497c-56b2-b823-33030ac0e398',
  'ac0ead02-afb4-5d4a-9da2-80bb4ea363ff',
  'abe56fa7-540a-5de6-9278-1b3f055d71a1',
  'abe24a88-efd0-5e17-a936-86fd0cfe7933',
  'abb81a46-b6ca-5490-9591-193621bac131',
  'ab5ec3c6-8342-59ae-b24b-0470047804eb',
  'aaade01b-3bc7-5d88-a935-fadc37f6cc81',
  'aaab331e-939b-5947-8e88-ed29e413d77a',
  'aa9d00d0-595a-57af-b1c7-4c5f6d13aded',
  'aa94b878-8668-52b8-b2c5-b0f2c748e26b',
  'aa661348-59e8-5985-98d7-d8ec4c74008c',
  'aa0f9827-5e29-57e1-b3b5-c00d91f8fcbc',
  'a9b73549-3ed4-5614-adfe-9bf2699886ea',
  'a92c2d90-c260-5ae9-9503-c6f07fb6139c',
  'a8f6805e-ac57-517b-a2cf-f98911af58fa',
  'a8b0e86f-9b7a-57d4-8c79-94636fd9e36e',
  'a8affcc2-f2ef-5d68-ae88-5dc173770df6',
  'a85b15cb-f7df-5b76-b6ec-8ec5fc0a62f0',
  'a8544ed3-4635-536b-9929-05eeba520ce8',
  'a84ff569-2f96-5e4f-828c-81e3176e06eb',
  'a7e9ae3b-9a36-53c4-af57-427bb311dfcd',
  'a7aabfb1-58bc-5ef3-a01d-474c079a3bd4',
  'a7985ff5-2319-53f7-9675-7e55199a9910',
  'a75e9c9b-ffd1-5e46-8e56-4ff11c5eeb58',
  'a758a745-fd01-5dd8-8581-0e7e8ef069ee',
  'a6cf4b7e-2e54-567c-8069-14bb7e1375d8',
  'a677e170-4e1c-5bca-b167-317f3cfc4d75',
  'a6148470-30d5-5a61-8ce4-f41bdc221b1c',
  'a5d4cdfb-f0dc-5644-81c6-04f9987a8a75',
  'a56a7864-e610-5a6f-91d2-8494d4b24c6f',
  'a56a4846-e612-5c21-8f9c-d1b50117d6f1',
  'a50043b5-aa1f-5b25-886e-6dbabd2c1f6e',
  'a4e1a31c-0067-5b7b-aec6-95834845ff10',
  'a440b54e-b650-553a-9aa2-ab3a4d83b157',
  'a435248d-b849-5437-91d5-8ce82a17e494',
  'a400d265-343f-5c15-af26-346dea6bbe50',
  'a3d63d64-6218-523d-b2a0-bfea32ad64fa',
  'a3afb5e7-c33c-511c-bc12-2d485dc984c7',
  'a356fd23-686d-572d-bad4-12bd491b5011',
  'a354c336-40d6-58f0-bde5-f8503905f662',
  'a34b5862-f177-5ceb-8711-1cc07e87cc36',
  'a2f589e1-8254-5513-9b45-3895b2e0c89e',
  'a2c7ca52-131e-5b05-b965-023d5d10c8be',
  'a2826581-1dba-5d17-be48-6341fd6f3a6b',
  'a269de3e-4224-5caf-80fb-621465597cf0',
  'a264075a-e899-554c-b873-0927bd7e0804',
  'a236504f-cdba-5837-8c60-4cd45a93c23e',
  'a22df089-88ce-5705-804f-3756ddc86fcc',
  'a1f5e29c-65fe-5d1b-bd85-9e314e57eb2b',
  'a1e74678-b8f7-5dc7-91c1-6024db952c50',
  'a1e6b34a-cd25-5208-8bc6-ec1c12827141',
  'a1d04a8a-0fe6-59cf-b346-ecddf36d751a',
  'a1cbb816-6300-52d1-a329-095ae5b38347',
  'a10672d1-5455-5aea-8cbd-caa65615b63d',
  'a0e1bb81-bd10-56d0-bcff-970cbdb728c1',
  'a0a40901-5799-502f-b24c-fde3ef08386f',
  'a07b68b6-4160-547f-8005-64935776998b',
  'a01a4606-9d88-5b95-a14d-318e83f40a54',
  'a0152462-14ad-58b8-bf03-b6cd35e446c7',
  '9f39c3a8-11f7-5eb9-bf83-dfb218036666',
  '9eaa1338-733d-5058-9d3e-e9090831a7b6',
  '9ea5eab2-d9dd-536c-a527-cae486145f99',
  '9e96c0ee-de01-51c6-bcf7-cd133af29e37',
  '9df4cafe-10e2-5375-a40f-16a1d916d514',
  '9d5d18fe-cf39-5914-ab57-4a2f8ef61124',
  '9d502f22-de27-5b76-865d-6a30a87b86f2'
);

    query plan

    Time: 100.268 ms  
  - planning: 2.225 ms  
  - execution: 98.043 ms  
    - I/O read: 96.370 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 290 (~2.30 MiB) from the buffer pool  
  - reads: 116 (~928.00 KiB) from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

Related to #444926 (closed)

Edited by Adam Cohen

Merge request reports