Fix segmented export functionality
What does this MR do and why?
While creating the export parts, we are iterating over all the vulnerability_reads
records ordered by traversal_ids
and vulnerability_id
. This means a higher value can be set as start_id
than end_id
.
For example for the the following tuples;
('{9970}', 10), ('{9970}', 11), ('{9970, 9972}', 1)
The start_id
will be 10 and the end_id
will be 1 as these records are first sorted by traversal_ids
.
For this reason, we need to compare the records by both traversal_ids
and vulnerability_id
while iterating over the records of the export part.
Database review
Here is the new query to iterate over vulnerability_reads
records for given export part;
SELECT
"vulnerability_reads".*
FROM
"vulnerability_reads"
WHERE
"vulnerability_reads"."traversal_ids" >= '{9970}'
AND "vulnerability_reads"."traversal_ids" < '{9971}'
AND "vulnerability_reads"."archived" = FALSE
AND ("vulnerability_reads"."traversal_ids",
"vulnerability_reads"."vulnerability_id") >= ('{9970}',
535)
AND ("vulnerability_reads"."traversal_ids",
"vulnerability_reads"."vulnerability_id") <= ('{9970,12345}',
585)
AND (("vulnerability_reads"."traversal_ids",
"vulnerability_reads"."vulnerability_id") > ('{9970}',
6538))
ORDER BY
"vulnerability_reads"."traversal_ids" ASC,
"vulnerability_reads"."vulnerability_id" ASC
LIMIT 1000
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/29279/commands/91071
Edited by Mehmet Emin INAC