Replace foreign keys to use the partitioned table - V2
Compare changes
Files
13+ 36
− 0
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)
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
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.This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.