Draft: Fix drifted project namespace parent_id and traversal_ids

What does this MR do and why?

Some project namespaces (rows in namespaces with type = 'Project') have a parent_id that no longer matches projects.namespace_id. The same write path could also leave organization_id out of sync with projects.organization_id; no such rows exist on GitLab.com today, but the migration handles that case as well. Both columns are in Namespaces::ProjectNamespace::SYNCED_ATTRIBUTES and are normally kept in sync by Project#ensure_project_namespace_in_sync, a before_validation callback. A write path that saved with validate: false skipped it. The root cause is tracked in #616993 (closed).

The traversal_ids of these rows are internally consistent with the stale parent_id. Two consequences:

  • Work item list views break for affected projects. NamespaceResolver full-path lookup fails silently.
  • WorkItems::TraversalIdsHealingCronWorker cannot fix them, because it trusts namespaces.traversal_ids.

This MR queues a batched background migration, FixDriftedProjectNamespaceParentId, over namespaces scoped to type = 'Project' (supporting index: index_namespaces_on_type_and_id). For each project namespace where parent_id IS DISTINCT FROM projects.namespace_id OR organization_id IS DISTINCT FROM projects.organization_id, it:

  1. sets parent_id = projects.namespace_id
  2. sets organization_id = projects.organization_id
  3. recomputes traversal_ids = parent.traversal_ids || id

Safety properties:

  • Project namespaces are hierarchy leaves, so only the single drifted row is recomputed. No descendant recalculation.
  • Parents whose own traversal_ids are inconsistent (traversal_ids[last] != id, including empty arrays) are skipped, so a wrong prefix is never propagated.
  • The UPDATE is a compare-and-swap. It re-checks parent_id and organization_id against the values observed in the CTE snapshot. A row changed by a concurrent writer (for example a project transfer) is left alone.
  • Idempotent. Aligned rows fall out of the filter.

Side effect worth noting: the existing trigger trigger_namespaces_traversal_ids_on_update fires when traversal_ids changes and inserts into namespaces_sync_events. ci_namespace_mirrors in the CI database is therefore realigned by the normal sync worker as well.

Batch settings: BATCH_SIZE = 20_000, SUB_BATCH_SIZE = 100, job_interval 2 minutes, gitlab_schema gitlab_main_org. Database testing measured 94.5 s per 20,000-row job (155.7 ms mean per 100-row sub-batch statement), inside the 2-minute interval, with an estimated 6 days 8 hours for the full sweep. 100,000 was tried and reverted because it works out to ~7–8 min per job.

References

Impact on GitLab.com

Original detection query (parent_id only):

SELECT p.id AS project_id,
       p.namespace_id AS expected_parent,
       n.id AS project_namespace_id,
       n.parent_id AS actual_parent
FROM projects p
JOIN namespaces n ON n.id = p.project_namespace_id
WHERE n.parent_id IS DISTINCT FROM p.namespace_id;

Confirmation query that mirrors the migration's exact filter (parent_id OR organization_id drift, type = 'Project', inner join to parent, parent traversal_ids guard):

SELECT n.id AS project_namespace_id,
       n.parent_id AS observed_parent_id,
       p.namespace_id AS correct_parent_id,
       (n.parent_id IS DISTINCT FROM p.namespace_id) AS parent_drift,
       (n.organization_id IS DISTINCT FROM p.organization_id) AS org_drift
FROM namespaces n
JOIN projects p ON p.project_namespace_id = n.id
JOIN namespaces parent ON parent.id = p.namespace_id
WHERE n.type = 'Project'
  AND (n.parent_id IS DISTINCT FROM p.namespace_id
    OR n.organization_id IS DISTINCT FROM p.organization_id)
  AND parent.traversal_ids[array_length(parent.traversal_ids, 1)] = parent.id
ORDER BY n.id;

Result on a Database Lab clone: 206 project namespaces on 2026-10-02 and 212 on 2026-10-09. Sessions:

Since 2026-10-02, 19 more project namespaces have drifted. Counts only: #634211

Breakdown from the 2026-10-02 run (same filter, counted by drift kind): parent_id only = 206, organization_id only = 0, both = 0. Every affected row is a parent_id drift; there is no organization_id drift on GitLab.com today, so the organization_id branch of the migration is a safeguard rather than a live fix.

The count went up between the two runs, so drift is still being created. The suspected source is the failure path of the async project transfer. Projects::TransferWorker cancels the transfer on an in-memory project namespace that still has the target parent_id, after the transfer service has rolled the project back. The fix is tracked separately in !262788 (merged) (issue #634211). This migration must run after that fix lands, or be requeued.

Migration query

The SQL the BBM runs per sub-batch, with real values for one sub-batch (batch 139190723–139210722, sub-batch 139200673–139200772, containing drifted namespace 139200723):

WITH candidates AS MATERIALIZED (
  SELECT n.id AS project_namespace_id,
         n.parent_id AS observed_parent_id,
         n.organization_id AS observed_organization_id,
         p.namespace_id AS correct_parent_id,
         p.organization_id AS correct_organization_id
  FROM (SELECT "namespaces"."id" FROM "namespaces"
        WHERE "namespaces"."id" BETWEEN 139190723 AND 139210722
          AND "namespaces"."type" = 'Project'
          AND "namespaces"."id" >= 139200673 AND "namespaces"."id" < 139200773) AS batch
  JOIN namespaces n ON n.id = batch.id
  JOIN projects p ON p.project_namespace_id = n.id
  WHERE n.type = 'Project'
    AND (n.parent_id IS DISTINCT FROM p.namespace_id
      OR n.organization_id IS DISTINCT FROM p.organization_id)
  LIMIT 100
), drifted AS MATERIALIZED (
  SELECT candidates.*, parent.traversal_ids AS parent_traversal_ids
  FROM candidates
  JOIN namespaces parent ON parent.id = candidates.correct_parent_id
  -- Skip parents whose own traversal_ids are inconsistent, so we do
  -- not propagate a wrong prefix. Also excludes an empty array.
  WHERE parent.traversal_ids[array_length(parent.traversal_ids, 1)] = parent.id
)
UPDATE namespaces
SET parent_id = drifted.correct_parent_id,
    organization_id = drifted.correct_organization_id,
    traversal_ids = drifted.parent_traversal_ids || namespaces.id
FROM drifted
WHERE namespaces.id = drifted.project_namespace_id
  -- Compare-and-swap: leave rows a concurrent writer changed since the
  -- CTE snapshot, so we do not overwrite a committed transfer.
  AND namespaces.parent_id IS NOT DISTINCT FROM drifted.observed_parent_id
  AND namespaces.organization_id IS NOT DISTINCT FROM drifted.observed_organization_id;

Query plans

The query finds drifted rows in their own MATERIALIZED CTE (candidates) and joins parent only for those, so a sub-batch with no drift does no parent lookups (suggested in review, with plans comparing both shapes: !251257 (comment 3917251370)). The plans that were here before were captured on older SQL and have been removed.

Plan A — common case: sub-batch with no drifted rows.

Pending: being re-run on the current SQL.

Plan A: no drifted rows

Pending.

Plan B — update case: sub-batch containing one drifted row.

Pending: being re-run on the current SQL.

Plan B: one drifted row

Pending.

Database testing

Earlier run (2026-09-16, on an older revision with BATCH_SIZE 10,000 and parent_id-only SQL): 38 batches sampled, 0 failed, average batch 45.76 s, mean 107 ms per sub-batch statement. Bot estimate for the full run: ~1 week 5 days. Pipeline: https://ops.gitlab.net/gitlab-com/database-team/gitlab-com-database-testing/-/pipelines/6245664

Previous run, on commit 67b371ed (BATCH_SIZE 20,000, current SQL), triggered via https://gitlab.com/gitlab-org/gitlab/-/jobs/16892236105. Results: !251257 (comment 3902845067) (pipeline https://ops.gitlab.net/gitlab-com/database-team/gitlab-com-database-testing/-/pipelines/6335340)

Metric Value
Batches sampled / failed 19 / 0
Average batch (job) time 94.5 s (18 of 19 in the 1–2 min bucket)
Sub-batch statement mean 155.7 ms, max 389 ms over 3,582 calls; 2 rows updated in the sample
Estimated time to complete 6 days, 8 hours
Queue migration 5.6 s, +0 B

The numbers above are from commit 67b371ed, before the two-CTE rewrite, so the test must be re-run.

Re-run on the current head (two-CTE SQL, commit 606e37e1) is in progress: https://gitlab.com/gitlab-org/gitlab/-/jobs/17229263885

Migration output

main: == 20261016102655 QueueFixDriftedProjectNamespaceParentId: migrating ==========
main: == 20261016102655 QueueFixDriftedProjectNamespaceParentId: migrated (0.0573s) =

How to set up and validate locally

Run the specs:

bundle exec rspec spec/lib/gitlab/background_migration/fix_drifted_project_namespace_parent_id_spec.rb spec/migrations/20261016102655_queue_fix_drifted_project_namespace_parent_id_spec.rb

MR acceptance checklist

  • Query plans for the migration statement added (both the no-drift and the update case)
  • db:gitlabcom-database-testing run on the current head (see Database testing)
  • Specs cover: parent_id drift, subgroup parent (multi-element prefix), organization_id-only drift, inconsistent parent skipped, aligned row untouched (parent_id and traversal_ids), non-project namespace untouched

Closes #618583

Edited by Aakriti Gupta

Merge request reports

Loading
Loading