Statement timeouts when updating vulnerability statistics during continuous vulnerability scans

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

• Close this issue

Summary

While running Continuous Vulnerability Scans, we've observed that the IngestVulnerabilityStatistics task times out. This has consistently been the only issue we've seen at the Postgres layer, and according to #439228 (comment 1740340800), it seems to be a known issue.

-- fingerprint 9ee55a6a0054d38c
INSERT INTO vulnerability_statistics
  AS target (project_id, letter_grade, created_at, updated_at, "info", "unknown", "low", "medium", "high", "critical")
  VALUES ($1, $2, now(), now(), $3, $4, $5, $6, $7, $8), ($9, $10, now(), now(), $11, $12, $13, $14, $15, $16), ($17, $18, now(), now(), $19, $20, $21, $22, $23, $24), ($25, $26, now(), now(), $27, $28, $29, $30, $31, $32), ($33, $34, now(), now(), $35, $36, $37, $38, $39, $40), ($41, $42, now(), now(), $43, $44, $45, $46, $47, $48), ($49, $50, now(), now(), $51, $52, $53, $54, $55, $56)
ON CONFLICT (project_id)
  DO UPDATE SET
    "info" = TARGET."info" + EXCLUDED."info", "unknown" = TARGET."unknown" + EXCLUDED."unknown", "low" = TARGET."low" + EXCLUDED."low", "medium" = TARGET."medium" + EXCLUDED."medium", "high" = TARGET."high" + EXCLUDED."high", "critical" = TARGET."critical" + EXCLUDED."critical",
    letter_grade = (SELECT (
  CASE
  WHEN TARGET.critical + EXCLUDED.critical > $57 THEN
    $58
  WHEN TARGET.high + TARGET.unknown + EXCLUDED.high + EXCLUDED.unknown > $59 THEN
    $60
  WHEN TARGET.medium + EXCLUDED.medium > $61 THEN
    $62
  WHEN TARGET.low + EXCLUDED.low > $63 THEN
    $64
  ELSE
    $65
  END
) as letter_grade
FROM
  (values (TARGET.critical, TARGET.unknown, TARGET.high, TARGET.medium, TARGET.low)) as TARGET (critical, unknown, high, medium, low),
  (values (EXCLUDED.critical, EXCLUDED.unknown, EXCLUDED.high, EXCLUDED.medium, EXCLUDED.low)) as EXCLUDED (critical, unknown, high, medium, low)
),
    updated_at = now()

Steps to reproduce

There aren't any steps that can be followed to consistently reproduce this behavior.

Possible fixes

• Rely on Vulnerabilities::Statistics::AdjustmentService to fix the discrepancies in the vulnerability statistics, and drop this task from the list of CVS tasks.
• Optimize the vulnerability statistics update for CVS.