Remove orphaned Vulnerabilities::Feedback records
What does this MR do and why?
This MR schedules a background migration to remove rows from vulnerability_feedback
table that have no corresponding rows in vulnerability_occurrences
table.
Related to #372251 (closed)
Database review
Total records count
Total count: 380 282
Total records affected
Rows without matching uuid
Count: 106724
SELECT COUNT(v_f.finding_uuid)
FROM vulnerability_feedback v_f
LEFT JOIN vulnerability_occurrences v_o
ON v_f.finding_uuid = v_o.uuid::uuid
WHERE v_o.uuid IS NULL
AND v_f.finding_uuid IS NOT NULL;
project_fingerprint
Rows without matching Count: 7638
SELECT COUNT(*)
FROM vulnerability_feedback v_f
LEFT JOIN vulnerability_occurrences v_o
ON v_f.project_fingerprint::bytea = v_o.project_fingerprint
WHERE v_f.finding_uuid IS NULL
AND v_o.id IS NULL;
Batch selection
EXPLAIN SELECT "vulnerability_feedback"."id"
FROM "vulnerability_feedback"
WHERE (
NOT EXISTS (
SELECT 1
FROM vulnerability_occurrences
WHERE vulnerability_occurrences.uuid = vulnerability_feedback.finding_uuid::VARCHAR
)
)
AND (
NOT EXISTS (
SELECT 1
FROM vulnerability_occurrences
WHERE vulnerability_occurrences.project_fingerprint = vulnerability_feedback.project_fingerprint::bytea
)
)
AND "vulnerability_feedback"."id" BETWEEN 1
AND 100
AND "vulnerability_feedback"."id" >= 1
ORDER BY "vulnerability_feedback"."id" ASC;
Plan: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/11881/commands/42250
Batch drop
DELETE
FROM "vulnerability_feedback"
WHERE (
NOT EXISTS (
SELECT 1
FROM vulnerability_occurrences
WHERE vulnerability_occurrences.uuid = vulnerability_feedback.finding_uuid::VARCHAR
)
)
AND (
NOT EXISTS (
SELECT 1
FROM vulnerability_occurrences
WHERE vulnerability_occurrences.project_fingerprint = vulnerability_feedback.project_fingerprint::bytea
)
)
AND "vulnerability_feedback"."id" BETWEEN 1
AND 100
AND "vulnerability_feedback"."id" >= 1
Query plan: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/11881/commands/42247
Additional notes
You can verify that Feedback will be kept if there's no match on the vulnerability_occurrences
table but there's a match on the security_findings
by adding
sast_scan_type = 1 # it's different because it's a Security::Scan object
ci_build = create_ci_build(project, user)
security_scan = create_security_scan(ci_build, sast_scan_type)
create_security_finding(
security_scan,
scanner,
uuid: nonexistent_uuid
)
after line 49 in spec/lib/gitlab/background_migration/drop_invalid_vulnerabilities_feedback_records_spec.rb
. The spec should fail because the count will decrease by 1 instead of 2
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.