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;

Rows without matching project_fingerprint

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.