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.