Skip to content

Identify corrupted foreign keys to p_ci_pipelines

In #483290 (comment 2094599130) we have identified a broken foreign key between p_ci_builds and p_ci_pipelines that was not cascading the deletes from the pipelines to the builds table. But I don't think that's the only broken FK.

These are all of the FK referencing this table in production:

Partitioned table "public.p_ci_pipelines"
...
Referenced by:
    TABLE "p_ci_pipelines" CONSTRAINT "fk_262d4c2d19_p" FOREIGN KEY (auto_canceled_by_partition_id, auto_canceled_by_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE SET NULL
    TABLE "ci_pipeline_chat_data" CONSTRAINT "fk_64ebfab6b3_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "p_ci_builds" CONSTRAINT "fk_87f4cefcda_p" FOREIGN KEY (upstream_pipeline_partition_id, upstream_pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "p_ci_builds" CONSTRAINT "fk_a2141b1522_p" FOREIGN KEY (auto_canceled_by_partition_id, auto_canceled_by_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE SET NULL
    TABLE "p_ci_builds" CONSTRAINT "fk_d3130c9a7f_p" FOREIGN KEY (partition_id, commit_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "ci_builds" CONSTRAINT "fk_d3130c9a7f_p_tmp" FOREIGN KEY (partition_id, commit_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID
    TABLE "gitlab_partitions_dynamic.ci_builds_101" CONSTRAINT "fk_d3130c9a7f_p_tmp" FOREIGN KEY (partition_id, commit_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID
    TABLE "gitlab_partitions_dynamic.ci_builds_102" CONSTRAINT "fk_d3130c9a7f_p_tmp" FOREIGN KEY (partition_id, commit_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID
    TABLE "ci_sources_pipelines" CONSTRAINT "fk_d4e29af7d7_p" FOREIGN KEY (source_partition_id, source_pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "ci_sources_pipelines" CONSTRAINT "fk_e1bad85861_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "p_ci_pipeline_variables" CONSTRAINT "fk_f29c5f4380_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "p_ci_stages" CONSTRAINT "fk_fb57e6cc56_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "ci_sources_projects" CONSTRAINT "fk_rails_10a1eb379a_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "ci_pipeline_metadata" CONSTRAINT "fk_rails_50c1e9ea10_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "ci_pipeline_messages" CONSTRAINT "fk_rails_8d3b04e3e1_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "p_ci_pipelines_config" CONSTRAINT "fk_rails_906c9a2533_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "ci_pipeline_artifacts" CONSTRAINT "fk_rails_a9e811a466_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "p_ci_builds_execution_configs" CONSTRAINT "fk_rails_c26408d02c_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "ci_daily_build_group_report_results" CONSTRAINT "fk_rails_ee072d13b3_p" FOREIGN KEY (partition_id, last_pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/31456/commands/97651

Edited by Marius Bobin