Resolve "Set traversal_ids for every namespace"

What does this MR do?

Background root migration

Gitlab::BackgroundMigration::BackfillNamespaceTraversalIdsRoots.new.perform(1, 100, 5)

  Gitlab::BackgroundMigration::BackfillNamespaceTraversalIdsRoots::Namespace Load (6.6ms)  SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."parent_id" IS NULL AND "namespaces"."id" BETWEEN 1 AND 100 AND (traversal_ids = '{}') ORDER BY "namespaces"."id" ASC LIMIT 1 /*application:console,line:/app/models/concerns/each_batch.rb:61:in `each_batch'*/
  Gitlab::BackgroundMigration::BackfillNamespaceTraversalIdsRoots::Namespace Load (0.5ms)  SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."parent_id" IS NULL AND "namespaces"."id" BETWEEN 1 AND 100 AND (traversal_ids = '{}') AND "namespaces"."id" >= 1 ORDER BY "namespaces"."id" ASC LIMIT 1 OFFSET 5 /*application:console,line:/app/models/concerns/each_batch.rb:80:in `block in each_batch'*/
  Gitlab::BackgroundMigration::BackfillNamespaceTraversalIdsRoots::Namespace Update All (3.7ms)  UPDATE "namespaces" SET traversal_ids = ARRAY[id] WHERE "namespaces"."parent_id" IS NULL AND "namespaces"."id" BETWEEN 1 AND 100 AND (traversal_ids = '{}') AND "namespaces"."id" >= 1 /*application:console,line:/lib/gitlab/background_migration/backfill_namespace_traversal_ids_roots.rb:27:in `block in perform'*/
  Gitlab::Database::BackgroundMigrationJob Update All (2.9ms)  UPDATE "background_migration_jobs" SET status = 1, updated_at = NOW() WHERE "background_migration_jobs"."status" = 0 AND "background_migration_jobs"."class_name" = 'BackfillNamespaceTraversalIdsRoots' AND (arguments = '[1,100,5]') /*application:console,line:/lib/gitlab/database/background_migration_job.rb:26:in `mark_all_as_succeeded'*/

Background children migration

Gitlab::BackgroundMigration::BackfillNamespaceTraversalIdsChildren.new.perform(1, 100, 5)

  Gitlab::BackgroundMigration::BackfillNamespaceTraversalIdsChildren::Namespace Load (0.6ms)  SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."parent_id" IS NOT NULL AND "namespaces"."id" BETWEEN 1 AND 100 ORDER BY "namespaces"."id" ASC LIMIT 1 /*application:console,line:/app/models/concerns/each_batch.rb:61:in `each_batch'*/
  Gitlab::BackgroundMigration::BackfillNamespaceTraversalIdsChildren::Namespace Load (0.4ms)  SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."parent_id" IS NOT NULL AND "namespaces"."id" BETWEEN 1 AND 100 AND "namespaces"."id" >= 3 ORDER BY "namespaces"."id" ASC LIMIT 1 OFFSET 5 /*application:console,line:/app/models/concerns/each_batch.rb:80:in `block in each_batch'*/
   (1.6ms)  UPDATE namespaces
SET traversal_ids = calculated_ids.traversal_ids
FROM (
  WITH RECURSIVE cte(source_id, namespace_id, parent_id, height) AS (
    (
      SELECT batch.id, batch.id, batch.parent_id, 1
      FROM (SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."parent_id" IS NOT NULL AND "namespaces"."id" BETWEEN 1 AND 100 AND "namespaces"."id" >= 3 AND "namespaces"."id" < 8) 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 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
)
 calculated_ids
WHERE namespaces.id = calculated_ids.id
  AND namespaces.traversal_ids = '{}'
 /*application:console,line:/lib/gitlab/background_migration/backfill_namespace_traversal_ids_children.rb:31:in `block in perform'*/

...

  Gitlab::Database::BackgroundMigrationJob Update All (0.4ms)  UPDATE "background_migration_jobs" SET status = 1, updated_at = NOW() WHERE "background_migration_jobs"."status" = 0 AND "background_migration_jobs"."class_name" = 'BackfillNamespaceTraversalIdsChildren' AND (arguments = '[1,100,5]') /*application:console,line:/lib/gitlab/database/background_migration_job.rb:26:in `mark_all_as_succeeded'*/

#325508 (closed)

Screenshots (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

• 📋 Does this MR need a changelog?
  • I have included a changelog entry.
  • I have not included a changelog entry because _____.
• Documentation (if required)
• Code review guidelines
• Merge request performance guidelines
• Style guides
• Database guides
• Separation of EE specific content

Availability and Testing

• Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
• Tested in all supported browsers
• Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

• Label as security and @ mention @gitlab-com/gl-security/appsec
• The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
• Security reports checked/validated by a reviewer from the AppSec team

Related to #325508 (closed)