Update `traversal_ids` of historical namespace statistics
What does this MR do and why?
This MR introduces changes to subscribe to traversal ID change events to update the existing historical namespace statistics records.
Related to Implement logic to keep `traversal_ids` column ... (#489999 - closed).
Database review
Initial batching query(EachBatch)
SELECT
"vulnerability_namespace_historical_statistics"."id"
FROM
"vulnerability_namespace_historical_statistics"
WHERE
"vulnerability_namespace_historical_statistics"."namespace_id" = 8306127
ORDER BY
"vulnerability_namespace_historical_statistics"."id" ASC
LIMIT 1
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32531/commands/100406
Finding the upper-bound of a batch(EachBatch)
SELECT
"vulnerability_namespace_historical_statistics"."id"
FROM
"vulnerability_namespace_historical_statistics"
WHERE
"vulnerability_namespace_historical_statistics"."namespace_id" = 8306127
AND "vulnerability_namespace_historical_statistics"."id" >= 1
ORDER BY
"vulnerability_namespace_historical_statistics"."id" ASC
LIMIT 1 OFFSET 100
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32531/commands/100408
The `update` query
The query updates at most 100 records.
UPDATE
"vulnerability_namespace_historical_statistics"
SET
"traversal_ids" = '{9970,8306127}'
WHERE
"vulnerability_namespace_historical_statistics"."namespace_id" = 8306127
AND "vulnerability_namespace_historical_statistics"."id" >= 1
AND "vulnerability_namespace_historical_statistics"."id" < 57841
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32531/commands/100403
Edited by Mehmet Emin INAC