Skip to content

Make MigrateSharedVulnerabilityIdentifiers use slow iteration

Dominic Bauer requested to merge 359963-slow-iteration into master

What does this MR do and why?

Changes the existing MigrateSharedVulnerabilityIdentifiers batched background migration to use slow iteration. The migration currently scopes batches. The migration was paused.

The migration appeared stuck in active state at 0%. Investigation revealed that the migration fails to execute further because the query that computes the next ID range times out. See:

The query that times out (postgres.ai):

SELECT
	DISTINCT "vulnerability_occurrences"."id"
FROM
	"vulnerability_occurrences"
	INNER JOIN "vulnerability_occurrence_identifiers" ON "vulnerability_occurrence_identifiers"."occurrence_id" = "vulnerability_occurrences"."id"
	INNER JOIN "vulnerability_identifiers" ON "vulnerability_identifiers"."id" = "vulnerability_occurrence_identifiers"."identifier_id"
WHERE
	"vulnerability_occurrences"."report_type" IN (7, 99)
	AND "vulnerability_occurrences"."project_id" != "vulnerability_identifiers"."project_id"
	AND "vulnerability_occurrences"."id" >= 234129291
ORDER BY
	"vulnerability_occurrences"."id" ASC
LIMIT
	1;

The reason for the statement timeout is that the migration has already processed all affected rows. In search for the next upper ID bound which never comes, the query reads through the whole tmp_idx_vulnerability_occurrences_on_id_where_report_type_7_99 index.

By switching to slow iteration, the PK index on vulnerability_occurrences is used for iteration, and the join is always bounded.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #359963 (closed)

Edited by Dominic Bauer

Merge request reports