Sync create indexes for ci_pipelines pipeline_id conversion
What does this MR do and why?
Sync create indexes for ci_pipelines pipeline_id conversion
This is the second step to synchronously create indexes for ci_pipelines
Async index creation is completed on prod. See this output from postgres.ai:
Table "public.ci_pipelines"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------------------------------+-----------------------------+-----------+----------+------------------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval('ci_pipelines_id_seq'::regclass) | plain | | |
ref | character varying(255) | | | | extended | | |
sha | character varying(255) | | | | extended | | |
before_sha | character varying(255) | | | | extended | | |
created_at | timestamp without time zone | | | | plain | | |
updated_at | timestamp without time zone | | | | plain | | |
tag | boolean | | | false | plain | | |
yaml_errors | text | | | | extended | | |
committed_at | timestamp without time zone | | | | plain | | |
project_id | integer | | | | plain | | |
status | character varying | | | | extended | | |
started_at | timestamp without time zone | | | | plain | | |
finished_at | timestamp without time zone | | | | plain | | |
duration | integer | | | | plain | | |
user_id | integer | | | | plain | | |
lock_version | integer | | | 0 | plain | | |
auto_canceled_by_id | integer | | | | plain | | |
pipeline_schedule_id | integer | | | | plain | | |
source | integer | | | | plain | | |
protected | boolean | | | | plain | | |
config_source | integer | | | | plain | | |
failure_reason | integer | | | | plain | | |
iid | integer | | | | plain | | |
merge_request_id | integer | | | | plain | | |
source_sha | bytea | | | | extended | | |
target_sha | bytea | | | | extended | | |
external_pull_request_id | bigint | | | | plain | | |
ci_ref_id | bigint | | | | plain | | |
locked | smallint | | not null | 1 | plain | | |
partition_id | bigint | | not null | | plain | | |
id_convert_to_bigint | bigint | | not null | 0 | plain | | |
auto_canceled_by_id_convert_to_bigint | bigint | | | | plain | | |
Indexes:
"ci_pipelines_pkey" PRIMARY KEY, btree (id)
"idx_ci_pipelines_artifacts_locked" btree (ci_ref_id, id) WHERE locked = 1
"index_ci_pipelines_for_ondemand_dast_scans" btree (id) WHERE source = 13
"index_ci_pipelines_on_auto_canceled_by_id" btree (auto_canceled_by_id)
"index_ci_pipelines_on_auto_canceled_by_id_bigint" btree (auto_canceled_by_id_convert_to_bigint)
"index_ci_pipelines_on_ci_ref_id_and_more" btree (ci_ref_id, id DESC, source, status) WHERE ci_ref_id IS NOT NULL
"index_ci_pipelines_on_external_pull_request_id" btree (external_pull_request_id) WHERE external_pull_request_id IS NOT NULL
"index_ci_pipelines_on_id_convert_to_bigint" UNIQUE, btree (id_convert_to_bigint)
"index_ci_pipelines_on_merge_request_id" btree (merge_request_id) WHERE merge_request_id IS NOT NULL
"index_ci_pipelines_on_pipeline_schedule_id_and_id" btree (pipeline_schedule_id, id)
"index_ci_pipelines_on_project_id_and_id_desc" btree (project_id, id DESC)
"index_ci_pipelines_on_project_id_and_iid" UNIQUE, btree (project_id, iid) WHERE iid IS NOT NULL
"index_ci_pipelines_on_project_id_and_ref_and_status_and_id" btree (project_id, ref, status, id)
"index_ci_pipelines_on_project_id_and_sha" btree (project_id, sha)
"index_ci_pipelines_on_project_id_and_source" btree (project_id, source)
"index_ci_pipelines_on_project_id_and_status_and_config_source" btree (project_id, status, config_source)
"index_ci_pipelines_on_project_id_and_status_and_created_at" btree (project_id, status, created_at)
"index_ci_pipelines_on_project_id_and_status_and_updated_at" btree (project_id, status, updated_at)
"index_ci_pipelines_on_project_id_and_user_id_and_status_and_ref" btree (project_id, user_id, status, ref) WHERE source <> 12
"index_ci_pipelines_on_project_idandrefandiddesc" btree (project_id, ref, id DESC)
"index_ci_pipelines_on_status_and_id" btree (status, id)
"index_ci_pipelines_on_user_id_and_created_at_and_config_source" btree (user_id, created_at, config_source)
"index_ci_pipelines_on_user_id_and_created_at_and_source" btree (user_id, created_at, source)
"index_ci_pipelines_on_user_id_and_id_and_cancelable_status" btree (user_id, id) WHERE status::text = ANY (ARRAY['running'::character varying::text, 'waiting_for_resource'::character varying::text, 'preparing'::character varying::text, 'pending'::character varying::text, 'created'::character varying::text, 'scheduled'::character varying::text])
"index_ci_pipelines_on_user_id_and_id_desc_and_user_not_verified" btree (user_id, id DESC) WHERE failure_reason = 3
Check constraints:
"check_d7e99a025e" CHECK (lock_version IS NOT NULL)
Foreign-key constraints:
"fk_190998ef09" FOREIGN KEY (external_pull_request_id) REFERENCES external_pull_requests(id) ON DELETE SET NULL
"fk_262d4c2d19" FOREIGN KEY (auto_canceled_by_id) REFERENCES ci_pipelines(id) ON DELETE SET NULL
"fk_3d34ab2e06" FOREIGN KEY (pipeline_schedule_id) REFERENCES ci_pipeline_schedules(id) ON DELETE SET NULL
"fk_d80e161c54" FOREIGN KEY (ci_ref_id) REFERENCES ci_refs(id) ON DELETE SET NULL
Referenced by:
TABLE "ci_pipeline_messages" CONSTRAINT "fk_0946fea681" FOREIGN KEY (pipeline_id_convert_to_bigint) REFERENCES ci_pipelines(id) ON DELETE CASCADE
TABLE "ci_pipelines" CONSTRAINT "fk_262d4c2d19" FOREIGN KEY (auto_canceled_by_id) REFERENCES ci_pipelines(id) ON DELETE SET NULL
TABLE "ci_pipeline_chat_data" CONSTRAINT "fk_5b21bde562" FOREIGN KEY (pipeline_id_convert_to_bigint) REFERENCES ci_pipelines(id) ON DELETE CASCADE
TABLE "p_ci_builds" CONSTRAINT "fk_87f4cefcda" FOREIGN KEY (upstream_pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
TABLE "p_ci_builds" CONSTRAINT "fk_a2141b1522" FOREIGN KEY (auto_canceled_by_id) REFERENCES ci_pipelines(id) ON DELETE SET NULL
TABLE "p_ci_builds" CONSTRAINT "fk_d3130c9a7f" FOREIGN KEY (commit_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
TABLE "ci_sources_pipelines" CONSTRAINT "fk_d4e29af7d7" FOREIGN KEY (source_pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
TABLE "ci_sources_pipelines" CONSTRAINT "fk_e1bad85861" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
TABLE "ci_pipeline_variables" CONSTRAINT "fk_f29c5f4380" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
TABLE "ci_stages" CONSTRAINT "fk_fb57e6cc56" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
TABLE "ci_sources_projects" CONSTRAINT "fk_rails_10a1eb379a" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
TABLE "ci_pipeline_metadata" CONSTRAINT "fk_rails_50c1e9ea10" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
TABLE "ci_pipeline_chat_data" CONSTRAINT "fk_rails_64ebfab6b3" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
TABLE "ci_pipeline_messages" CONSTRAINT "fk_rails_8d3b04e3e1" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
TABLE "ci_pipelines_config" CONSTRAINT "fk_rails_906c9a2533" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
TABLE "ci_pipeline_artifacts" CONSTRAINT "fk_rails_a9e811a466" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
TABLE "ci_daily_build_group_report_results" CONSTRAINT "fk_rails_ee072d13b3" FOREIGN KEY (last_pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
TABLE "ci_pipeline_variables" CONSTRAINT "temp_fk_rails_8d3b04e3e1" FOREIGN KEY (pipeline_id_convert_to_bigint) REFERENCES ci_pipelines(id) ON DELETE CASCADE
Triggers:
ci_pipelines_loose_fk_trigger AFTER DELETE ON ci_pipelines REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records()
trigger_1bd97da9c1a4 BEFORE INSERT OR UPDATE ON ci_pipelines FOR EACH ROW EXECUTE FUNCTION trigger_1bd97da9c1a4()
trigger_b2d852e1e2cb BEFORE INSERT OR UPDATE ON ci_pipelines FOR EACH ROW EXECUTE FUNCTION trigger_b2d852e1e2cb()
Access method: heap
Options: autovacuum_enabled=true, toast.autovacuum_enabled=trueScreenshots 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.
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #422808 (closed)
Edited by Tianwen Chen