Resolve "Make sure the `Vulnerability#state` accurately reflects the actual state."

What does this MR do and why?

This MR migrates records from vulnerability_feedback into vulnerability_state_transitions in so that we can deprecate and (later on) remove the vulnerability_feedback table and Vulnerabilities::Feedback model.

This background migration copies Vulnerabilities::Feedback records if their feedback_type is dismissal.

Related to #324893 (closed)

Database review

Rows affected

SQL

SELECT COUNT(*) FROM vulnerability_feedback
WHERE EXISTS (SELECT 1 FROM vulnerability_occurrences WHERE vulnerability_occurrences.uuid = vulnerability_feedback.finding_uuid::varchar)
OR EXISTS (SELECT 1 FROM vulnerability_occurrences WHERE vulnerability_occurrences.project_fingerprint = vulnerability_feedback.project_fingerprint::bytea)
OR EXISTS (SELECT 1 FROM security_findings WHERE security_findings.uuid = vulnerability_feedback.finding_uuid)
AND feedback_type = 0;

Batch selection

SQL

SELECT "vulnerability_feedback"."id"
FROM "vulnerability_feedback"
WHERE (
    EXISTS (
        SELECT 1
        FROM vulnerability_occurrences
        WHERE vulnerability_occurrences.uuid = vulnerability_feedback.finding_uuid::varchar
        )
    OR EXISTS (
        SELECT 1
        FROM vulnerability_occurrences
        WHERE vulnerability_occurrences.project_fingerprint = vulnerability_feedback.project_fingerprint::bytea
    )
    OR EXISTS(
        SELECT 1
        FROM security_findings
        WHERE security_findings.uuid = vulnerability_feedback.finding_uuid
    )
)
AND "vulnerability_feedback"."feedback_type" = 0
AND "vulnerability_feedback"."id" BETWEEN 1 AND 1000
AND "vulnerability_feedback"."id" >= 1
ORDER BY "vulnerability_feedback"."id" ASC
LIMIT 1000
OFFSET 0;

Cold cache: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/12253/commands/43481

Warm cache: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/12253/commands/43482

I imagine 8s might look slow but the buffers look okay to me and I'm usually told that the production is significantly faster. That said reducing the batch size is not a problem.

Inserting a StateTransition entry

SQL

INSERT INTO "vulnerability_state_transitions" ("vulnerability_id", "to_state", "from_state", "created_at", "updated_at", "author_id", "comment", "dismissal_reason")
VALUES (2990073, 2, 1, NOW(), NOW(), 487608, 'test comment', 4)
RETURNING "id";

Cold cache: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/12253/commands/43483

Warm cache: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/12253/commands/43484

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.