Add backfill migrations for ci namespace/project mirrors
What does this MR do and why?
This MR adds two backfill migrations to populate ci_namespace_mirrors
and ci_project_mirrors
tables.
This MR based on !75517 (merged).
DB
UP
== 20211208122200 ScheduleBackfillCiNamespaceMirrors: migrating ===============
-- Scheduled 1 BackfillCiNamespaceMirrors jobs with a maximum of 1000 records per batch and an interval of 120 seconds.
The migration is expected to take at least 120 seconds. Expect all jobs to have completed after 2021-12-08 10:04:19 UTC."
== 20211208122200 ScheduleBackfillCiNamespaceMirrors: migrated (0.1242s) ======
== 20211208122201 ScheduleBackfillCiProjectMirrors: migrating =================
-- Scheduled 1 BackfillCiProjectMirrors jobs with a maximum of 1000 records per batch and an interval of 120 seconds.
The migration is expected to take at least 120 seconds. Expect all jobs to have completed after 2021-12-08 10:04:20 UTC."
== 20211208122201 ScheduleBackfillCiProjectMirrors: migrated (0.0916s) ========
DOWN
== 20211208122201 ScheduleBackfillCiProjectMirrors: reverting =================
== 20211208122201 ScheduleBackfillCiProjectMirrors: reverted (0.0000s) ========
== 20211208122200 ScheduleBackfillCiNamespaceMirrors: reverting ===============
== 20211208122200 ScheduleBackfillCiNamespaceMirrors: reverted (0.0000s) ======
INSERT Query 1
INSERT INTO ci_project_mirrors (project_id, namespace_id)
SELECT "projects"."id", "projects"."namespace_id" FROM "projects" WHERE "projects"."id" BETWEEN 24000 AND 25000
ON CONFLICT (project_id) DO NOTHING;
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7536/commands/26785
INSERT Query 2
INSERT INTO ci_namespace_mirrors (namespace_id, traversal_ids)
WITH RECURSIVE cte(source_id, namespace_id, parent_id, height) AS (
(
SELECT batch.id, batch.id, batch.parent_id, 1
FROM (SELECT "namespaces"."id", "namespaces"."parent_id" FROM "namespaces" WHERE "namespaces"."id" BETWEEN 24000 AND 25000) AS batch
)
UNION ALL
(
SELECT cte.source_id, n.id, n.parent_id, cte.height+1
FROM namespaces n, cte
WHERE n.id = cte.parent_id
)
)
SELECT flat_hierarchy.source_id as namespace_id,
array_agg(flat_hierarchy.namespace_id ORDER BY flat_hierarchy.height DESC) as traversal_ids
FROM (SELECT * FROM cte FOR UPDATE) flat_hierarchy
GROUP BY flat_hierarchy.source_id
ON CONFLICT (namespace_id) DO NOTHING;
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7536/commands/26786
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.
Edited by Furkan Ayhan