Skip to content

Update foreign key references to `ci_builds` to use the routing table

Problem statement

There are a few tables that have foreign key references to the ci_builds table:

Current FK Temp FK added? Temp FK async valid? Temp generally FK valid? FK swapped? Available to self-managed
TABLE "ci_build_pending_states" CONSTRAINT "fk_861cd17da3_p" ✅ ✅ ✅ ✅ ✅
TABLE "ci_build_trace_chunks" CONSTRAINT "fk_89e29fa5ee_p" ✅ ✅ ✅ ✅ ✅
TABLE "ci_unit_test_failures" CONSTRAINT "fk_9e0fc58930_p" ✅ ✅ ✅ ✅ ✅
TABLE "ci_sources_pipelines" CONSTRAINT "fk_be5624bf37_p" ✅ ✅ ✅ ✅ ✅
TABLE "ci_resources" CONSTRAINT "fk_e169a8e3d5_p" ✅ ✅ ✅ ✅ ✅
TABLE "ci_build_report_results" CONSTRAINT "fk_rails_16cb1ff064_p" ✅ ✅ ✅ ✅ ✅
TABLE "ci_build_needs" CONSTRAINT "fk_rails_3cf221d4ed_p" ✅ ✅ ✅ ✅ ✅
TABLE "ci_builds_runner_session" CONSTRAINT "fk_rails_70707857d3_p" ✅ ✅ ✅ ✅ ✅
TABLE "ci_pending_builds" CONSTRAINT "fk_rails_725a2644a3_p" ✅ ✅ ✅ ✅ ✅
TABLE "ci_build_trace_metadata" CONSTRAINT "fk_rails_aebc78111f_p" ✅ ✅ ✅ ✅ ✅
TABLE "ci_job_artifacts" CONSTRAINT "fk_rails_c5137cb2c1_p" ✅ ✅ ✅ ✅ ✅
TABLE "ci_running_builds" CONSTRAINT "fk_rails_da45cfa165_p" ✅ ✅ ✅ ✅ ✅
TABLE "ci_job_variables" CONSTRAINT "fk_rails_fbf3b34792_p" ✅ ✅ ✅ ✅ ✅
TABLE "p_ci_runner_machine_builds" CONSTRAINT "fk_bb490f12fe_p" ✅ ✅ ✅ ✅ ✅
TABLE "p_ci_builds_metadata" CONSTRAINT "fk_e20479742e_p" ✅ ✅ ✅ ✅ ✅

Legend:

  • ✅ - completed
  • ⏳ - in dev/review
  • ♻ - awaiting deployment/async processing

Merge requests that require self-managed counterparts:

GitLab.com self-managed
!123799 (merged) !124154 (merged)
!123355 (merged) !123805 (merged)
!123811 (merged) !124306 (merged)
!124186 (merged) !124303 (merged)
!124310 (merged) !124311 (merged)

The ci_job_artifacts table references ci_builds directly and we won't be able to use a new partition for p_ci_builds since inserting a record into ci_job_artifacts will fail to validate the current FK. This is valid for all the tables in the list.

https://docs.gitlab.com/ee/architecture/blueprints/ci_data_decay/pipeline_partitioning.html#between-regular-tables-and-routing-tables

Proposal

Replace the FKs targeting ci_builds with FKs targeting the routing table, p_ci_builds.

Implementation details

For non-partitioned tables

  • add temporary foreign key as non-valid on .com/dev
  • enqueue and wait for async validations
  • add temporary foreign key + sync validations for self-managed
  • drop the old FKs and rename the temporary ones to the old names on .com/dev
  • drop the old FKs and rename the temporary ones to the old names for self-managed

For partitioned tables

  • add temporary foreign key from partitioned tables' partitions to p_ci_builds as non-valid on .com/dev
  • enqueue and wait for async validations
  • add temporary foreign key from partitioned tables to p_ci_builds on .com/dev
  • add temporary foreign key + sync validations for self-managed
  • drop the old FKs and rename the temporary ones to the old names on .com/dev
  • drop the old FKs and rename the temporary ones to the old names for self-managed
Edited by Marius Bobin