Prevent duplicate pipeline IIDs -- Next step: BBM to fix existing duplicate iids in p_ci_pipelines

Background

We currently have the unique index (project_id, iid, partition_id) on the table p_ci_pipelines. This ensures uniqueness within the same partition but not across partitions. Due to a possible workflow bug/race condition, we occasionally save duplicate pipeline iids for the same project, which causes problems such as https://gitlab.com/gitlab-com/request-for-help/-/issues/2855#note_2518831537.

We've explored a couple different possible solutions (https://gitlab.com/gitlab-org/gitlab/-/issues/545167#note_2802709422, https://gitlab.com/gitlab-org/gitlab/-/issues/545167#note_2844491364), and we ultimately decided that the only way to guarantee Pipeline iid uniqueness on the database level is to create a new table to track the iids. (See proposed approach in !210789 (comment 2869213142).)

• In !213065 (merged), we introduced the static hash partitioned table p_ci_pipeline_iids. It has 64 partitions with the hash on project_id and primary key on (project_id, iid).
• In !213457 (merged), we added database triggers on p_ci_pipelines so that it keep track of iids in p_ci_pipeline_iids.
• In !213992 (merged), we are updating the internal ID initial_value logic to read maximum(:iid) from ci_pipeline_iids instead of ci_pipelines.

Implementation

This issue is intended to track the next task in our implementation steps.

Proposal

Create a BBM to fix existing duplicate iids in p_ci_pipelines. You might follow an approach similar to !47081 (merged).

• One important difference in approach is that that all duplicate iids must be updated to a new one. This is because the DB trigger that executes when p_ci_pipelines.iid is updated always deletes the existing p_ci_pipeline_iids record. So this means that we can't just leave one instance of the duplicate iid in the table because the corresponding pipeline iid record won't exist.

This BBM must wait until #582338 (closed) is complete and finalized before starting.