Sync create indexes and prepare FK for p_ci_builds commit_id
What does this MR do and why?
Sync create indexes and prepare FK for p_ci_builds commit_id
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)
-
gitlab_partitions_dynamic.ci_builds_101
at https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/26121/commands/82206- "index_8de48efee4" btree (resource_group_id, status, commit_id_convert_to_bigint) WHERE resource_group_id IS NOT NULL
- "index_19fdfdf488" btree (commit_id_convert_to_bigint, stage_idx, created_at)
- "index_db48796375" btree (runner_id_convert_to_bigint, id DESC)
-
ci_builds
at https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/26121/commands/82205- "index_bc23fb9243" btree (resource_group_id, status, commit_id_convert_to_bigint) WHERE resource_group_id IS NOT NULL
- "index_d46de3aa4f" btree (commit_id_convert_to_bigint, stage_idx, created_at)
- "index_adafd086ad" btree (runner_id_convert_to_bigint, id DESC)
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
-
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
-
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_8d588a7095" FOREIGN KEY (commit_id_convert_to_bigint) REFERENCES ci_pipelines(id) ON DELETE CASCADE \d gitlab_partitions_dynamic.ci_builds_101 "fk_8d588a7095" FOREIGN KEY (commit_id_convert_to_bigint) REFERENCES ci_pipelines(id) ON DELETE CASCADE
-
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) and #422836 (closed)