Replace foreign keys to use the partitioned table - V2

What does this MR do and why?

This reintroduces the migrations that we no-oped from !121718 (merged).

This changes them to automatically skip the ones that would not be possible to run because of the wraparound autovacuum process.

Related to #414396 (closed)

Screenshots or screen recordings

Output when there is a wraparound vacuum going on ci_builds:

$ RAILS_ENV=test bin/rails db:migrate:ci
ci: == [advisory_lock_connection] object_id: 224340, pg_backend_pid: 82744
ci: == 20230612075608 ReplaceCiJobArtifactsForeignKeyV2: migrating ================
ci: -- Wraparound prevention vacuum detected, skipping migration
ci: == 20230612075608 ReplaceCiJobArtifactsForeignKeyV2: migrated (0.0673s) =======

ci: == 20230612083337 ReplaceCiRunningBuildsForeignKeyV2: migrating ===============
ci: -- Wraparound prevention vacuum detected, skipping migration
ci: == 20230612083337 ReplaceCiRunningBuildsForeignKeyV2: migrated (0.0100s) ======

ci: == 20230612083655 ReplaceCiJobVariablesForeignKeyV2: migrating ================
ci: -- Wraparound prevention vacuum detected, skipping migration
ci: == 20230612083655 ReplaceCiJobVariablesForeignKeyV2: migrated (0.0091s) =======

ci: == 20230612083810 ReplacePCiBuildsMetadataForeignKeyV2: migrating =============
ci: -- Wraparound prevention vacuum detected, skipping migration
ci: == 20230612083810 ReplacePCiBuildsMetadataForeignKeyV2: migrated (0.0096s) ====

ci: == 20230612084013 ReplacePCiRunnerMachineBuildsForeignKeyV2: migrating ========
ci: -- Wraparound prevention vacuum detected, skipping migration
ci: == 20230612084013 ReplacePCiRunnerMachineBuildsForeignKeyV2: migrated (0.0110s)

ci: == [advisory_lock_connection] object_id: 224340, pg_backend_pid: 82744

Output when there is a wraparound vacuum going on other table, say ci_job_variables:

$ RAILS_ENV=test bin/rails db:migrate:ci
ci: == [advisory_lock_connection] object_id: 224340, pg_backend_pid: 86982
ci: == 20230612075608 ReplaceCiJobArtifactsForeignKeyV2: migrating ================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("LOCK TABLE p_ci_builds, ci_job_artifacts IN SHARE ROW EXCLUSIVE MODE")
ci:    -> 0.0005s
ci: -- execute("ALTER TABLE ci_job_artifacts ADD CONSTRAINT temp_fk_rails_c5137cb2c1_p FOREIGN KEY (partition_id, job_id) REFERENCES p_ci_builds (partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0011s
ci: == 20230612075608 ReplaceCiJobArtifactsForeignKeyV2: migrated (0.0970s) =======

ci: == 20230612083337 ReplaceCiRunningBuildsForeignKeyV2: migrating ===============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("LOCK TABLE p_ci_builds, ci_running_builds IN SHARE ROW EXCLUSIVE MODE")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE ci_running_builds ADD CONSTRAINT temp_fk_rails_da45cfa165_p FOREIGN KEY (partition_id, build_id) REFERENCES p_ci_builds (partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0006s
ci: == 20230612083337 ReplaceCiRunningBuildsForeignKeyV2: migrated (0.0246s) ======

ci: == 20230612083655 ReplaceCiJobVariablesForeignKeyV2: migrating ================
ci: -- Wraparound prevention vacuum detected, skipping migration
ci: == 20230612083655 ReplaceCiJobVariablesForeignKeyV2: migrated (0.0090s) =======

ci: == 20230612083810 ReplacePCiBuildsMetadataForeignKeyV2: migrating =============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("LOCK TABLE p_ci_builds, public.ci_builds_metadata IN SHARE ROW EXCLUSIVE MODE")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE public.ci_builds_metadata ADD CONSTRAINT temp_fk_e20479742e_p FOREIGN KEY (partition_id, build_id) REFERENCES p_ci_builds (partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0006s
ci: == 20230612083810 ReplacePCiBuildsMetadataForeignKeyV2: migrated (0.0374s) ====

ci: == 20230612084013 ReplacePCiRunnerMachineBuildsForeignKeyV2: migrating ========
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("LOCK TABLE p_ci_builds, gitlab_partitions_dynamic.ci_runner_machine_builds_100 IN SHARE ROW EXCLUSIVE MODE")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE gitlab_partitions_dynamic.ci_runner_machine_builds_100 ADD CONSTRAINT temp_fk_bb490f12fe_p FOREIGN KEY (partition_id, build_id) REFERENCES p_ci_builds (partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0006s
ci: == 20230612084013 ReplacePCiRunnerMachineBuildsForeignKeyV2: migrated (0.0346s)

ci: == [advisory_lock_connection] object_id: 224340, pg_backend_pid: 86982

How to set up and validate locally

While it is possible to trigger wraparound prevention autovacuum on a thin clone, it's hard to do it on a development/test database and it's duration will not be enough to test the migrations, but these steps can be used locally:

$ bin/rails c -e test

# we're using this to replace the view with a table and have the option to insert data into it:
require_relative 'spec/support/helpers/database/database_helpers.rb'
include Database::DatabaseHelpers
swapout_view_for_table(:postgres_autovacuum_activity, connection: Ci::ApplicationRecord.connection)

Gitlab::Database::SharedModel.using_connection(Ci::ApplicationRecord.connection) { Gitlab::Database::PostgresAutovacuumActivity.create(table_identifier: 'public.ci_job_variables', schema: 'public', table: 'ci_job_variables', wraparound_prevention: true) }

To reset after testing:

• git checkout db/structure.sql
• git checkout master
• ./scripts/regenerate-schema, make sure to close the rails console before.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.