Clean up job artifact files on loose FK deletion

Shabini Rajadasrequested to merge
604201-clean-orphaned-artifact-files-on-loose-fk-deletion into master

What does this MR do and why?

When a project is deleted, any leftover p_ci_job_artifacts rows are removed by the loose foreign key cleanup (LooseForeignKeys::CleanupWorker), which issues a bulk DELETE — and the p_ci_job_artifacts.job_id -> p_ci_builds ON DELETE CASCADE constraint removes more artifact rows when builds are deleted. These paths delete the database rows but never the files, because artifact files are only removed once their deletion is recorded in ci_deleted_objects and drained by Ci::DeleteObjectsWorker. The result is orphaned artifact files on storage with no database reference (surfaced by gitlab:cleanup:orphan_job_artifact_files).

This MR adds a file-aware cleaner for the p_ci_job_artifacts loose foreign key. It captures the files into ci_deleted_objects (the same two-phase pattern as Ci::JobArtifacts::DestroyBatchService) before deleting the rows, so the files are removed by Ci::DeleteObjectsWorker instead of being orphaned.

The cleaner is selected through a new optional cleaner_class loose foreign key config option (allowlisted, mirroring the existing worker_class option) and is gated behind the loose_foreign_keys_clean_job_artifact_files ops feature flag (default disabled), so it can be rolled out and disabled safely. With the flag off, behaviour is unchanged.

Database queries

This MR adds these queries for the custom cleaner:

SELECT "p_ci_job_artifacts".*
FROM "p_ci_job_artifacts"
WHERE "p_ci_job_artifacts"."project_id" IN (...)
LIMIT 500
FOR UPDATE SKIP LOCKED;

EXPLAIN: https://console.postgres.ai/gitlab/projects/gitlab-production-ci/sessions/53736/commands/156044

DELETE FROM "p_ci_job_artifacts"
WHERE "p_ci_job_artifacts"."id" IN (...)
  AND "p_ci_job_artifacts"."partition_id" IN (...);

EXPLAIN: https://console.postgres.ai/gitlab/projects/gitlab-production-ci/sessions/53736/commands/156065

How to set up and validate locally

  1. Enable the flag: Feature.enable(:loose_foreign_keys_clean_job_artifact_files).
  2. Create a project with job artifacts, then delete the project.
  3. After the loose foreign key cleanup runs, confirm ci_deleted_objects rows are created for the artifacts (and the files are removed once Ci::DeleteObjectsWorker runs), instead of being left orphaned on storage.

Known follow-up (out of scope)

This covers the direct p_ci_job_artifacts -> projects path (e.g. orphaned artifacts whose build is already gone). Artifacts removed via the p_ci_builds -> projects loose foreign key cascading through ON DELETE CASCADE before the artifact cleaner runs are not yet covered, because the framework does not guarantee cross-table ordering. This is tracked as a follow-up.

References

Edited by Michael Kozono

Merge request reports