Update pipeline iid init value logic to read from new p_ci_pipeline_iids table

What does this MR do and why?

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. 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.

This MR

In this MR, we now update the Ci::Pipeline model's init function so that it checks the max iid value in the new table p_ci_pipeline_iids. This init function is called by InternalId#initial_value to recalculate last_value when the record doesn't already exist (which can happen when we run InternalId.flush_records! or when a new project is created.) The following changes were made in the new logic:

• Refactors the existing logic a bit so that it always falls back to the pipelines count if a maximum(:iid) value cannot be obtained.
• Cleans up the code so it uses .where(**scope) instead of a long chain of associations (e.g. pipeline.project&.all_pipelines&.count). The SQL for pipeline count remains the same.
• Gets the maximum iid value from p_ci_pipeline_iids.

This change is made behind a feature flag: update_init_iid_to_read_from_ci_pipeline_iids. Roll-out issue: #581989

⚠️ This feature flag will not be rolled out until after we fully migrate existing iids to p_ci_pipeline_iids in !213722.

• This is because if the maximum iid in p_ci_pipelines is out of sync with p_ci_pipeline_iids, we could potentially get stuck in a loop where the newly recalculated max iid is lower than the max iid in p_ci_pipelines and so the next iid generated may already exist.
• While likely very rare, this scenario could currently exist if InternalId#last_value was reset to a wrong value at a specific time before the new database triggers took effect. So to mitigate this risk, we won't enable this FF until after the backfill BBM completes.

Next steps:

1. Create a BBM to backfill existing iids into p_ci_pipeline_iids.
2. Create a BBM to fix existing duplicate iids.

References

• Solves the next step in https://gitlab.com/gitlab-org/gitlab/-/issues/545167+. See implementation table.
• Feature flag roll-out issue: [FF] `update_init_iid_to_read_from_ci_pipeline_... (#581989)
• Backfill MR: Backfill p_ci_pipeline_iids with existing iids ... (!213722)

Database query plans

Maximum iid query

Before

pipeline.project&.all_pipelines&.maximum(:iid) 

::Ci::Pipeline.where(**scope).maximum(:iid)

SELECT MAX("p_ci_pipelines"."iid")
FROM "p_ci_pipelines"
WHERE "p_ci_pipelines"."project_id" = 278964

Query plan: https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/45850/commands/140331

• Query plan reads from every partition.

After

::Ci::PipelineIid.where(**scope).maximum(:iid)

SELECT MAX("p_ci_pipeline_iids"."iid")
FROM "p_ci_pipeline_iids"
WHERE "p_ci_pipeline_iids"."project_id" =278964

Query plan: https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/45850/commands/140332

• Query plan only needs to read from one partition.

Pipeline count query

• This query has not changed from before. Included here for completeness.

::Ci::Pipeline.where(**scope).count

SELECT COUNT(*)
FROM "p_ci_pipelines"
WHERE "p_ci_pipelines"."project_id" = 68540063;

Query plan: https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/45850/commands/140330

How to set up and validate locally

1. In a new project, you can verify that creating a new pipeline multiple times succeeds as usual both before and after enabling the feature flag:

Feature.enable(:update_init_iid_to_read_from_ci_pipeline_iids)

2. You can also confirm the behaviour when a duplicate iid is attempted to be inserted into a different partition. Follow the steps in !213457 (merged).

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.