Sync create indexes and prepare FK for p_ci_builds upstream_pipeline_id

What does this MR do and why?

This is the second step to synchronously create indexes for p_ci_builds See https://docs.gitlab.com/ee/development/database/adding_database_indexes.html#add-a-migration-to-create-the-index-synchronously

And prepare the foreign key to prevent invalid records See https://docs.gitlab.com/ee/development/database/add_foreign_key_to_existing_column.html#prevent-invalid-records

The corresponding indexes are created asynchronously from MR !142763 (merged)

• For gitlab_partitions_dynamic.ci_builds_101:

  • "index_0e156a994a" btree (upstream_pipeline_id_convert_to_bigint) WHERE upstream_pipeline_id_convert_to_bigint IS NOT NULL
  • "index_726c5ae963" btree (commit_id_convert_to_bigint, type, ref)
  • "index_564638cbc9" btree (commit_id_convert_to_bigint, 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]))

• For ci_builds:

  • "index_89477d6012" btree (upstream_pipeline_id_convert_to_bigint) WHERE upstream_pipeline_id_convert_to_bigint IS NOT NULL

  • "index_fc42f73fa6" btree (commit_id_convert_to_bigint, type, ref)

  • "index_357cc39ca4" btree (commit_id_convert_to_bigint, 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]))

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

1. Run the following commands:

   ./scripts/regenerate-schema
   RAILS_ENV=test bin/rails runner 'Feature.enable :database_async_foreign_key_validation'
   RAILS_ENV=test bin/rails runner 'Feature.enable :ci_current_partition_value_101'
   RAILS_ENV=test bin/rake gitlab:db:validate_async_constraints:all

2. Confirm the following foreign keys are created and validated in TEST environment

   psql gitlabhq_test -h $PATH_TO_POSTGRESQL/gitlab-development-kit/postgresql

   \d ci_builds
   "fk_6b6c3f3e70" FOREIGN KEY (upstream_pipeline_id_convert_to_bigint) REFERENCES ci_pipelines(id) ON DELETE CASCADE
   \d gitlab_partitions_dynamic.ci_builds_101
   "fk_6b6c3f3e70" FOREIGN KEY (upstream_pipeline_id_convert_to_bigint) REFERENCES ci_pipelines(id) ON DELETE CASCADE

3. Run the following commands to restore the feature flag:

   RAILS_ENV=test bin/rails runner 'Feature.disable :ci_current_partition_value_101'
   RAILS_ENV=test bin/rails runner 'Feature.disable :database_async_foreign_key_validation'

Related to #422835 (closed) #422836 (closed)