Delete orphaned states in helm metadata cache backfill (!246021) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

Fixes the FinalizeHkBackfillPackagesHelmMetadataCacheStatesProjectId migration failure that broke the 18.11 → 19.0 upgrade (gitlab-org/gitlab#605940, sev1).

BackfillPackagesHelmMetadataCacheStatesProjectId copies project_id from the parent packages_helm_metadata_caches into the child packages_helm_metadata_cache_states. The child has a hard FK fk_2902beee34 (project_id → projects.id), but the parent has no FK to projects — project cleanup for the parent is done via a loose foreign key. So a parent cache can hold a project_id that references a deleted project (when the loose-FK cleanup has not run), and copying that dangling value into the child aborts the migration with:

PG::ForeignKeyViolation: insert or update on table "packages_helm_metadata_cache_states"
violates foreign key constraint "fk_2902beee34"

Fix

Make the job orphan-tolerant. Per sub-batch it now:

Deletes the orphaned parent packages_helm_metadata_caches rows whose project_id references a project that no longer exists. The child state is removed automatically via fk_rails_281379b415 (packages_helm_metadata_cache_id → packages_helm_metadata_caches.id) ON DELETE CASCADE.
Backfills the remaining states, as before (super).

Why delete the parent, not just the child state: the state cannot be backfilled (the value would violate fk_2902beee34) and must not be left NULL (20260525120003 adds a validated NOT NULL constraint check_4afb385a32). Deleting only the child is also not enough — Geo recreates it (Geo::VerifiableModel#save_verification_details and Geo::VerificationStateBackfillWorker), and the sharding-key trigger copies the dangling project_id back, so the re-insert hits the same fk_2902beee34 violation. Removing the orphaned parent eliminates the row Geo would otherwise keep re-tracking.

The finalize migration re-invokes the current job code, so this fix lets the finalize complete without altering the already-shipped migration files. The approach mirrors BackfillShardingKeyAndCleanLabelLinksTable (backfill valid rows + delete un-backfillable orphans in one job).

Note: the raw SQL delete does not remove the orphaned cache's object-storage file. That blob is already garbage (its project is deleted) and object-storage cleanup is not performed in migrations; it is a negligible, pre-existing leak compared to breaking the upgrade / Geo.

Migration queries

Per sub-batch (SUB_BATCH_SIZE = 100) the job runs a new delete of orphaned parent caches, then the unchanged backfill from BackfillDesiredShardingKeyJob.

New — delete orphaned parent caches for NULL-project states in the sub-batch (cascades to the child state). Uses stacked MATERIALIZED CTEs with LIMIT so the planner cannot flip the projects lookup into a hash anti-join over a sequential scan of projects — it stays a primary-key probe at any size (see the plan analysis in the thread):

WITH sub_batch AS MATERIALIZED (
  SELECT * FROM packages_helm_metadata_cache_states WHERE id BETWEEN <lo> AND <hi> LIMIT 100
),
filtered_sub_batch AS MATERIALIZED (
  SELECT * FROM sub_batch WHERE project_id IS NULL LIMIT 100
),
to_delete AS MATERIALIZED (
  SELECT packages_helm_metadata_caches.id
  FROM filtered_sub_batch
  JOIN packages_helm_metadata_caches
    ON packages_helm_metadata_caches.id = filtered_sub_batch.packages_helm_metadata_cache_id
  LEFT JOIN projects ON projects.id = packages_helm_metadata_caches.project_id
  WHERE projects.id IS NULL
  LIMIT 100
)
DELETE FROM packages_helm_metadata_caches
WHERE id IN (SELECT id FROM to_delete);

Unchanged — backfill the remaining states:

UPDATE packages_helm_metadata_cache_states
SET project_id = packages_helm_metadata_caches.project_id
FROM packages_helm_metadata_caches
WHERE packages_helm_metadata_caches.id = packages_helm_metadata_cache_states.packages_helm_metadata_cache_id
  AND packages_helm_metadata_cache_states.id IN (
    SELECT id FROM packages_helm_metadata_cache_states
    WHERE project_id IS NULL AND id BETWEEN <lo> AND <hi>
  );

Both statements are bounded by the sub-batch of packages_helm_metadata_cache_states.id (≤ 100 ids), and projects is accessed by primary key. Verified on a seeded test DB (200k projects): the CTE form runs an Index Only Scan using projects_pkey (~0.2 ms) and stays stable across sub-batch sizes, whereas a plain NOT EXISTS flips to a full Seq Scan on projects on wider windows. Plans posted in the thread.

Backport

Needs backporting to 19-1-stable and 19-0-stable for the GitLab Dedicated / self-managed internal release requested in #605940. Ruby-only change (job class + spec); no schema change.

How to set up and validate locally

Specs

bundle exec rspec spec/lib/gitlab/background_migration/backfill_packages_helm_metadata_cache_states_project_id_spec.rb

New specs cover backfilling when the project exists, deleting the orphaned cache (cascading to its state) when the project no longer exists, and leaving already-backfilled states untouched.

Manual end-to-end reproduction (test DB)

Run this against the disposable test database so nothing in your dev environment is touched — restore it afterwards with RAILS_ENV=test bundle exec rails db:test:prepare.

GDK is not a Geo primary, so packages_helm_metadata_cache_states is never populated automatically (Geo::VerifiableModel#save_verification_details returns early unless Gitlab::Geo.primary?). No patch to the job is needed — it operates on the state table directly — we just seed the rows by hand. The seed bypasses the sharding-key trigger and the NOT NULL check so an un-backfilled (project_id IS NULL) row can be inserted for an orphaned parent, mirroring the state on the affected tenant. In a fresh test DB the projects table is empty, so any project_id is orphaned.

1. Seed an orphaned cache + un-backfilled state — gdk psql -d gitlabhq_test:

BEGIN;
DROP TRIGGER IF EXISTS trigger_489fffe04425 ON packages_helm_metadata_cache_states;
ALTER TABLE packages_helm_metadata_cache_states DROP CONSTRAINT IF EXISTS check_4afb385a32;

-- parent cache pointing at a non-existent project (no FK to projects, so allowed)
INSERT INTO packages_helm_metadata_caches
  (project_id, size, status, file_store, channel, file, object_storage_key, created_at, updated_at)
VALUES (999999999, 1, 0, 1, 'stable', 'index.yaml', 'orphan/key', now(), now());

-- un-backfilled state for that cache
INSERT INTO packages_helm_metadata_cache_states
  (packages_helm_metadata_cache_id, verification_state, verification_retry_count, project_id)
SELECT id, 0, 0, NULL FROM packages_helm_metadata_caches WHERE object_storage_key = 'orphan/key';

-- restore the trigger (present in production); leave the NOT NULL check off, since
-- during the real upgrade the finalize (19.0) runs before that migration (19.1)
CREATE TRIGGER trigger_489fffe04425 BEFORE INSERT OR UPDATE ON packages_helm_metadata_cache_states
  FOR EACH ROW EXECUTE FUNCTION trigger_489fffe04425();
COMMIT;

2. Roll back the finalize and the queue migrations. Rolling back only the finalize is not enough — its down is a no-op, the batched migration stays finished, and re-running the finalize returns immediately without reprocessing. The queue migration's down deletes the batched-migration record, so re-running it re-queues over the current table (now including the orphan):

RAILS_ENV=test bundle exec rails db:migrate:down:main VERSION=20260420233038   # finalize (no-op down)
RAILS_ENV=test bundle exec rails db:migrate:down:main VERSION=20260318153238   # queue (deletes BBM)

(GDK is multi-db, so the :main suffix is required. On a single-db setup use db:migrate:down VERSION=….)

3. Reproduce the failure on master — re-run migrations. The queue migration recreates the batched migration over the seeded orphan, then the finalize runs it synchronously and aborts:

RAILS_ENV=test bundle exec rails db:migrate
# == 20260420233038 FinalizeHkBackfillPackagesHelmMetadataCacheStatesProjectId: migrating
# StandardError: An error has occurred, all later migrations canceled:
# PG::ForeignKeyViolation: ERROR: insert or update on table
#   "packages_helm_metadata_cache_states" violates foreign key constraint "fk_2902beee34"

This is the same stack as #605940. The queue migration is now re-applied and only the finalize remains pending.

4. Verify the fix — check out this branch and re-run migrations. The fixed job deletes the orphaned parent cache (cascading its state), so the finalize completes:

RAILS_ENV=test bundle exec rails db:migrate                # finalize succeeds

Confirm the orphan is gone — gdk psql -d gitlabhq_test:

SELECT count(*) FROM packages_helm_metadata_caches       WHERE object_storage_key = 'orphan/key';  -- 0
SELECT count(*) FROM packages_helm_metadata_cache_states WHERE project_id IS NULL;                  -- 0

5. (Optional) confirm the follow-up NOT NULL migration now succeeds — gdk psql -d gitlabhq_test:

ALTER TABLE packages_helm_metadata_cache_states
  ADD CONSTRAINT check_4afb385a32 CHECK (project_id IS NOT NULL) NOT VALID;
ALTER TABLE packages_helm_metadata_cache_states VALIDATE CONSTRAINT check_4afb385a32;  -- succeeds

6. Restore the test DB:

RAILS_ENV=test bundle exec rails db:test:prepare

Faster alternative: invoke the job directly (skips the migration rollback)

# RAILS_ENV=test bundle exec rails runner — runs the same statement the finalize executes
r = ApplicationRecord.connection.select_one(
  "SELECT min(id) lo, max(id) hi FROM packages_helm_metadata_cache_states WHERE project_id IS NULL")
Gitlab::BackgroundMigration::BackfillPackagesHelmMetadataCacheStatesProjectId.new(
  start_id: r['lo'], end_id: r['hi'],
  batch_table: :packages_helm_metadata_cache_states, batch_column: 278964,
  sub_batch_size: 100, pause_ms: 0,
  connection: ApplicationRecord.connection,
  job_arguments: [:project_id, :packages_helm_metadata_caches, :project_id, :packages_helm_metadata_cache_id]
).perform
# master => PG::ForeignKeyViolation on fk_2902beee34; this branch => no error, orphan removed

Related to gitlab-org/gitlab#605940

Edited Jul 17, 2026 by Stan Hu

Delete orphaned states in helm metadata cache backfill