Orphaned job artifact files: loose-FK cleanup on project deletion removes p_ci_job_artifacts rows but not their files

Summary

Follow-up to #545050 (closed) (closed). On project deletion, job artifact files can be orphaned: the p_ci_job_artifacts rows are removed but the files are never deleted. Seen on 18.11.2-ee / PostgreSQL 17.8 — which has the full #545050 (closed) fix (18.1.0 + 18.4.0) and is past PG bug #508672 — so this is a remaining code gap. Reported via gitlab-com/request-for-help#4873.

Root cause

projects (main DB) can't hold a real FK on CI tables, so cleanup runs via loose foreign keys (p_ci_job_artifacts → projects and p_ci_builds → projects, both on_delete: async_delete). On project deletion, LooseForeignKeys::CleanupWorker runs delete_all on the rows (plus the p_ci_job_artifacts.job_id → p_ci_builds ON DELETE CASCADE). That removes records at the DB level only — it doesn't use DestroyBatchService/fast_destroy and never enqueues ci_deleted_objects, so the files are stranded with their original created_at date.

This path is only meant as a safety net behind Projects::DestroyService#destroy_ci_records! (which deletes files synchronously first). When that synchronous cleanup doesn't fully complete (large project, error/timeout, or artifacts uploaded mid-deletion), rows reach the loose-FK net with files intact → orphaned files.

Evidence

  • Ci::JobArtifact.find_by(id: <id>) => nil (row gone) while the file is still on disk.
  • Ci::DeletedObject.group("date_trunc('hour', pick_up_at)").count => {} (never queued for deletion).

Why #545050 (closed) doesn't cover it

#545050 (closed) fixed the ordering in the synchronous destroy_ci_records! path (18.1.0 + 18.4.0 reorder). It does not address the asynchronous loose-FK / cascade path, which still deletes rows without files.

Proposal

Loose-FK / cascade deletions of file-backed CI tables should route file cleanup through ci_deleted_objects / Ci::DeleteObjectsService rather than delete_all, which cannot delete files.

Related

  • Follow-up to #545050 (closed) (closed)
  • Reported via gitlab-com/request-for-help#4873
Edited by 🤖 GitLab Bot 🤖