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.
NamespaceResolverfull-path lookup fails silently. WorkItems::TraversalIdsHealingCronWorkercannot fix them, because it trustsnamespaces.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:
- sets
parent_id = projects.namespace_id - sets
organization_id = projects.organization_id - 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_idsare 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_idandorganization_idagainst 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
- Closes #618583
- Original investigation: #614002 (closed). A production scan on 2026-08-20 found 187 affected projects (parent_id only): #614002 (comment 3710490949)
- Root cause gap in
ensure_project_namespace_in_sync: #616993 (closed) - The one known row (gitlab-org/theseus) was fixed manually via gitlab-com/gl-infra/production#22742 (comment 3714777519). The rest were not.
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:
- 2026-10-02 (206): https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/60472/commands/165602 (full scan, ~24 min on the clone; this is a one-off check, not something the migration runs)
- 2026-10-09 (212): https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/62527/commands/167921
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.rbMR acceptance checklist
- Query plans for the migration statement added (both the no-drift and the update case)
-
db:gitlabcom-database-testingrun 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