Skip to content

Add backfill migrations for ci namespace/project mirrors

Furkan Ayhan requested to merge backfill-ci-namespace-project-mirrors into master

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.

Edited by Furkan Ayhan

Merge request reports