Skip to content

Convert primary keys on ci_pipelines to bigint before partitioning

It looks like it's more complex/impossible without downtime to change a primary key after the table is partitioned since we can't use our current workflow on partitioned tables:

PG::FeatureNotSupported: ERROR:  ALTER TABLE / ADD CONSTRAINT USING INDEX is not supported on partitioned tables

The primary key for ci_pipelines is still using integer type and we need to have it converted to bigint before starting to partition that table.

This will take 4 releases(milestones) to be completed.

Process

Following the process outlined here, we must:

Initialize the conversion !118136 (merged) workflowcomplete %16.0
Prepare the bigint index and foreign keys !148034 (merged) & !148132 (merged) workflowcomplete %17.0
Swap the columns !121123 (merged) workflowcomplete %17.0
Remove the trigger and old integer columns !152722 (merged) workflowcomplete %17.1
Remove ignore rules !153554 (merged) workflowcomplete %17.2

In order to swap the columns, we will need to convert the foreign keys referencing ci_pipelines(id) to bigint:

Issue Progress Target Milestone
ci_pipeline_chat_data.pipeline_id: #412862 (closed) &11283 (closed) Done in %16.7 %16.5
ci_pipeline_messages.pipeline_id: #412863 (closed) &11278 (closed) Done in %16.7 %16.5
p_ci_builds.upstream_pipeline_id: #412873 (closed) &11285 (closed) Swapped %17.0
p_ci_builds.commit_id: #412872 (closed) &11285 (closed) Swapped %17.0
p_ci_builds.auto_canceled_by_id: #412871 (closed) &11285 (closed) Swapped %17.0
ci_stages.pipeline_id: #412870 (closed) &11281 (closed) Done in %16.7 %16.5
ci_sources_pipelines.source_pipeline_id: #412869 (closed) &11282 (closed) Done in %16.7 %16.5
ci_sources_pipelines.pipeline_id: #412866 (closed) &11282 (closed) Done in %16.7 %16.5
ci_pipeline_variables.pipeline_id: #412864 (closed) &11279 (closed) Done in %16.8 %16.5
ci_pipelines.auto_canceled_by_id: #412865 (closed) &11280 (closed) Done in %16.8 %16.5
merge_requests.head_pipeline_id &13384 (closed) Swapped %17.2
merge_request_metrics.head_pipeline_id &13384 (closed) Swapped %17.2
packages_build_infos.pipeline_id &13384 (closed) Swapped %17.2
merge_trains.head_pipeline_id &13384 (closed) Swapped %17.2
vulnerability_feedback.pipeline_id &13384 (closed) Swapped %17.2
vulnerability_occurrence_pipelines.head_pipeline_id &13384 (closed) Swapped %17.2
Edited by Tianwen Chen