Sync FK `fk_rails_494e57ee78_p`
What does this MR do and why?
Sync FK fk_rails_494e57ee78_p
The foreign key has been verified on postgres.ai and it works as expected.
Click to expand the setup script
gitlabhq_dblab=# ALTER TABLE
gitlabhq_dblab-# public.ci_builds VALIDATE CONSTRAINT fk_rails_494e57ee78_p;
ALTER TABLE
gitlabhq_dblab=#
gitlabhq_dblab=# ALTER TABLE
gitlabhq_dblab-# gitlab_partitions_dynamic.ci_builds_101 VALIDATE CONSTRAINT fk_rails_494e57ee78_p;
ALTER TABLE
gitlabhq_dblab=#
gitlabhq_dblab=# ALTER TABLE
gitlabhq_dblab-# gitlab_partitions_dynamic.ci_builds_102 VALIDATE CONSTRAINT fk_rails_494e57ee78_p;
ALTER TABLE
gitlabhq_dblab=#
gitlabhq_dblab=# ALTER TABLE
gitlabhq_dblab-# p_ci_builds
gitlabhq_dblab-# ADD
gitlabhq_dblab-# CONSTRAINT fk_rails_494e57ee78_p FOREIGN KEY (
gitlabhq_dblab(# auto_canceled_by_partition_id,
gitlabhq_dblab(# auto_canceled_by_id
gitlabhq_dblab(# ) REFERENCES p_ci_pipelines (partition_id, id) ON UPDATE CASCADE ON DELETE
gitlabhq_dblab-# SET
gitlabhq_dblab-# NULL;
ALTER TABLE
gitlabhq_dblab=#
gitlabhq_dblab=# ALTER TABLE
gitlabhq_dblab-# p_ci_builds VALIDATE CONSTRAINT fk_rails_494e57ee78_p;
ALTER TABLE
gitlabhq_dblab=#
gitlabhq_dblab=# \d p_ci_builds
Partitioned table "public.p_ci_builds"
Column | Type | Collation | Nullable | Default
--------------------------------+-----------------------------+-----------+----------+---------
status | character varying(255) | | |
finished_at | timestamp without time zone | | |
created_at | timestamp without time zone | | |
updated_at | timestamp without time zone | | |
started_at | timestamp without time zone | | |
coverage | double precision | | |
name | character varying(255) | | |
options | text | | |
allow_failure | boolean | | not null | false
stage_idx | integer | | |
tag | boolean | | |
ref | character varying(255) | | |
type | character varying(255) | | |
target_url | character varying(255) | | |
description | character varying(255) | | |
erased_at | timestamp without time zone | | |
artifacts_expire_at | timestamp without time zone | | |
environment | character varying | | |
when | character varying | | |
yaml_variables | text | | |
queued_at | timestamp without time zone | | |
lock_version | integer | | | 0
coverage_regex | character varying | | |
retried | boolean | | |
protected | boolean | | |
failure_reason | integer | | |
scheduled_at | timestamp with time zone | | |
token_encrypted | character varying | | |
resource_group_id | bigint | | |
waiting_for_resource_at | timestamp with time zone | | |
processed | boolean | | |
scheduling_type | smallint | | |
id | bigint | | not null |
stage_id | bigint | | |
partition_id | bigint | | not null |
auto_canceled_by_partition_id | bigint | | |
auto_canceled_by_id | bigint | | |
commit_id | bigint | | |
erased_by_id | bigint | | |
project_id | bigint | | |
runner_id | bigint | | |
trigger_request_id | bigint | | |
upstream_pipeline_id | bigint | | |
user_id | bigint | | |
execution_config_id | bigint | | |
upstream_pipeline_partition_id | bigint | | |
Partition key: LIST (partition_id)
Indexes:
"p_ci_builds_pkey" PRIMARY KEY, btree (id, partition_id)
"index_p_ci_builds_on_execution_config_id" btree (execution_config_id) WHERE execution_config_id IS NOT NULL
"p_ci_builds_auto_canceled_by_id_idx" btree (auto_canceled_by_id) WHERE auto_canceled_by_id IS NOT NULL
"p_ci_builds_commit_id_artifacts_expire_at_id_idx" btree (commit_id, artifacts_expire_at, id) WHERE type::text = 'Ci::Build'::text AND (retried = false OR retried IS NULL) AND (name::text = ANY (ARRAY['sast'::character varying::text, 'secret_detection'::character varying::text, 'dependency_scanning'::character varying::text, 'container_scanning'::character varying::text, 'dast'::character varying::text]))
"p_ci_builds_commit_id_stage_idx_created_at_idx" btree (commit_id, stage_idx, created_at)
"p_ci_builds_commit_id_status_type_idx" btree (commit_id, status, type)
"p_ci_builds_commit_id_type_name_ref_idx" btree (commit_id, type, name, ref)
"p_ci_builds_commit_id_type_ref_idx" btree (commit_id, type, ref)
"p_ci_builds_name_id_idx" btree (name, id) WHERE (name::text = ANY (ARRAY['container_scanning'::character varying::text, 'dast'::character varying::text, 'dependency_scanning'::character varying::text, 'license_management'::character varying::text, 'sast'::character varying::text, 'secret_detection'::character varying::text, 'coverage_fuzzing'::character varying::text, 'license_scanning'::character varying::text, 'apifuzzer_fuzz'::character varying::text, 'apifuzzer_fuzz_dnd'::character varying::text])) AND type::text = 'Ci::Build'::text
...skipping...
"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
"fk_d3130c9a7f_p" FOREIGN KEY (partition_id, commit_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
"fk_rails_4540ead625_p" FOREIGN KEY (upstream_pipeline_partition_id, upstream_pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
"fk_rails_494e57ee78_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
"fk_rails_d739f46384_p" FOREIGN KEY (partition_id, commit_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
TABLE "ci_build_pending_states" CONSTRAINT "fk_861cd17da3_p" FOREIGN KEY (partition_id, build_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ci_build_trace_chunks" CONSTRAINT "fk_89e29fa5ee_p" FOREIGN KEY (partition_id, build_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ci_unit_test_failures" CONSTRAINT "fk_9e0fc58930_p" FOREIGN KEY (partition_id, build_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "p_ci_runner_machine_builds" CONSTRAINT "fk_bb490f12fe_p" FOREIGN KEY (partition_id, build_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ci_sources_pipelines" CONSTRAINT "fk_be5624bf37_p" FOREIGN KEY (source_partition_id, source_job_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ci_resources" CONSTRAINT "fk_e169a8e3d5_p" FOREIGN KEY (partition_id, build_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE SET NULL
TABLE "p_ci_builds_metadata" CONSTRAINT "fk_e20479742e_p" FOREIGN KEY (partition_id, build_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "p_ci_build_sources" CONSTRAINT "fk_rails_023578ae70" FOREIGN KEY (partition_id, build_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ci_build_report_results" CONSTRAINT "fk_rails_16cb1ff064_p" FOREIGN KEY (partition_id, build_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ci_build_needs" CONSTRAINT "fk_rails_3cf221d4ed_p" FOREIGN KEY (partition_id, build_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ci_builds_runner_session" CONSTRAINT "fk_rails_70707857d3_p" FOREIGN KEY (partition_id, build_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ci_pending_builds" CONSTRAINT "fk_rails_725a2644a3_p" FOREIGN KEY (partition_id, build_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "p_ci_build_trace_metadata" CONSTRAINT "fk_rails_aebc78111f_p" FOREIGN KEY (partition_id, build_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "p_ci_build_names" CONSTRAINT "fk_rails_bc221a297a" FOREIGN KEY (partition_id, build_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "p_ci_job_artifacts" CONSTRAINT "fk_rails_c5137cb2c1_p" FOREIGN KEY (partition_id, job_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "p_ci_job_annotations" CONSTRAINT "fk_rails_d4d0c0fa0f" FOREIGN KEY (partition_id, job_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "p_ci_build_tags" CONSTRAINT "fk_rails_d7bd025909" FOREIGN KEY (partition_id, build_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ci_running_builds" CONSTRAINT "fk_rails_da45cfa165_p" FOREIGN KEY (partition_id, build_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ci_job_variables" CONSTRAINT "fk_rails_fbf3b34792_p" FOREIGN KEY (partition_id, job_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
assign_p_ci_builds_id_trigger BEFORE INSERT ON p_ci_builds FOR EACH ROW EXECUTE FUNCTION assign_p_ci_builds_id_value()
p_ci_builds_loose_fk_trigger AFTER DELETE ON p_ci_builds REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records_override_table('p_ci_builds')
Number of partitions: 3 (Use \d+ to list them.)Click to expand the verification output
gitlabhq_dblab=# begin;
BEGIN
gitlabhq_dblab=*# select id, auto_canceled_by_id, auto_canceled_by_partition_id from p_ci_builds where auto_canceled_by_id is not null order by auto_canceled_by_id desc limit 1; id | auto_canceled_by_id | auto_canceled_by_partition_id
------------+---------------------+-------------------------------
9232966814 | 1688746277 | 102
(1 row)
gitlabhq_dblab=*# delete from p_ci_pipelines where id=1688746277 and partition_id = 102;
DELETE 1
gitlabhq_dblab=*# select id, auto_canceled_by_id, auto_canceled_by_partition_id from p_ci_builds where id=9232966814 and partition_id = 102;
id | auto_canceled_by_id | auto_canceled_by_partition_id
------------+---------------------+-------------------------------
9232966814 | |
(1 row)
gitlabhq_dblab=*# rollback;
ROLLBACKPlease include cross links to any resources that are relevant to this MR. This will give reviewers and future readers helpful context to give an efficient review of the changes introduced.
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Screenshots or screen recordings
Screenshots are required for UI changes, and strongly recommended for all other merge requests.
| Before | After |
|---|---|
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
Related to #498860 (closed) #498865 (closed)
Edited by Tianwen Chen