Module: Gitlab::Database::MigrationHelpers
- Includes:
- AsyncConstraints::MigrationHelpers, AsyncIndexes::MigrationHelpers, DynamicModelHelpers, WraparoundVacuumHelpers, Gitlab::Database::Migrations::BackgroundMigrationHelpers, Gitlab::Database::Migrations::BatchedBackgroundMigrationHelpers, Gitlab::Database::Migrations::ConstraintsHelpers, Gitlab::Database::Migrations::ExtensionHelpers, Gitlab::Database::Migrations::LockRetriesHelpers, Gitlab::Database::Migrations::RedisHelpers, Gitlab::Database::Migrations::ReestablishedConnectionStack, Gitlab::Database::Migrations::SidekiqHelpers, Gitlab::Database::Migrations::TimeoutHelpers, PartitionHelpers, RenameTableHelpers
- Included in:
- BackgroundMigration::LegacyUploadsMigrator, CascadingNamespaceSettings, V2, PartitioningMigrationHelpers::IndexHelpers, PartitioningMigrationHelpers::TableManagementHelpers, PartitioningMigrationHelpers::UniquenessHelpers
- Defined in:
- lib/gitlab/database/migration_helpers.rb,
lib/gitlab/database/migration_helpers/v2.rb,
lib/gitlab/database/migration_helpers/swapping.rb,
lib/gitlab/database/migration_helpers/announce_database.rb,
lib/gitlab/database/migration_helpers/convert_to_bigint.rb,
lib/gitlab/database/migration_helpers/wraparound_autovacuum.rb,
lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb,
lib/gitlab/database/migration_helpers/loose_foreign_key_helpers.rb,
lib/gitlab/database/migration_helpers/wraparound_vacuum_helpers.rb,
lib/gitlab/database/migration_helpers/cascading_namespace_settings.rb,
lib/gitlab/database/migration_helpers/automatic_lock_writes_on_tables.rb
Defined Under Namespace
Modules: AnnounceDatabase, AutomaticLockWritesOnTables, CascadingNamespaceSettings, ConvertToBigint, LooseForeignKeyHelpers, RestrictGitlabSchema, Swapping, V2, WraparoundAutovacuum, WraparoundVacuumHelpers
Constant Summary collapse
- INTEGER_IDS_YET_TO_INITIALIZED_TO_BIGINT_FILE_PATH =
'db/integer_ids_not_yet_initialized_to_bigint.yml'
- TABLE_INT_IDS_YAML_FILE_COMMENT =
<<-MESSAGE.strip_heredoc # -- DON'T MANUALLY EDIT -- # Contains the list of integer IDs which were converted to bigint for new installations in # https://gitlab.com/gitlab-org/gitlab/-/issues/438124, but they are still integers for existing instances. # On initialize_conversion_of_integer_to_bigint those integer IDs will be removed automatically from here. MESSAGE
- PENDING_INT_IDS_ERROR_MSG =
"'%{table}' table still has %{int_ids} integer IDs. "\ "Please include them in the 'columns' param and in your backfill migration. "\ "For more info: https://gitlab.com/gitlab-org/gitlab/-/issues/482470"
- ENFORCE_INITIALIZE_ALL_INT_IDS_FROM_MILESTONE =
'17.4'
- DEFAULT_TIMESTAMP_COLUMNS =
%i[created_at updated_at].freeze
Constants included from DynamicModelHelpers
DynamicModelHelpers::BATCH_SIZE
Constants included from Gitlab::Database::Migrations::RedisHelpers
Gitlab::Database::Migrations::RedisHelpers::SCAN_START_CURSOR
Constants included from Gitlab::Database::Migrations::SidekiqHelpers
Gitlab::Database::Migrations::SidekiqHelpers::DEFAULT_MAX_ATTEMPTS, Gitlab::Database::Migrations::SidekiqHelpers::DEFAULT_TIMES_IN_A_ROW
Constants included from Gitlab::Database::Migrations::ConstraintsHelpers
Gitlab::Database::Migrations::ConstraintsHelpers::MAX_IDENTIFIER_NAME_LENGTH
Constants included from Gitlab::Database::Migrations::BatchedBackgroundMigrationHelpers
Gitlab::Database::Migrations::BatchedBackgroundMigrationHelpers::BATCH_CLASS_NAME, Gitlab::Database::Migrations::BatchedBackgroundMigrationHelpers::BATCH_MIN_DELAY, Gitlab::Database::Migrations::BatchedBackgroundMigrationHelpers::BATCH_MIN_VALUE, Gitlab::Database::Migrations::BatchedBackgroundMigrationHelpers::BATCH_SIZE, Gitlab::Database::Migrations::BatchedBackgroundMigrationHelpers::EARLY_FINALIZATION_ERROR, Gitlab::Database::Migrations::BatchedBackgroundMigrationHelpers::ENFORCE_EARLY_FINALIZATION_FROM_VERSION, Gitlab::Database::Migrations::BatchedBackgroundMigrationHelpers::NonExistentMigrationError, Gitlab::Database::Migrations::BatchedBackgroundMigrationHelpers::SUB_BATCH_SIZE
Constants included from Gitlab::Database::Migrations::BackgroundMigrationHelpers
Gitlab::Database::Migrations::BackgroundMigrationHelpers::BATCH_SIZE, Gitlab::Database::Migrations::BackgroundMigrationHelpers::JOB_BUFFER_SIZE
Instance Method Summary collapse
-
#add_concurrent_foreign_key(source, target, column:, **options) ⇒ Object
Adds a foreign key with only minimal locking on the tables involved.
-
#add_concurrent_index(table_name, column_name, options = {}) ⇒ Object
Creates a new index, concurrently.
- #add_primary_key_using_index(table_name, pk_name, index_to_use) ⇒ Object
- #add_sequence(table_name, column_name, sequence_name, start_value) ⇒ Object
-
#add_timestamps_with_timezone(table_name, options = {}) ⇒ Object
Adds ‘created_at` and `updated_at` columns with timezone information.
-
#backfill_conversion_of_integer_to_bigint(table, columns, primary_key: :id, batch_size: 20_000, sub_batch_size: 1000, pause_ms: 100, interval: 2.minutes) ⇒ Object
Backfills the new columns used in an integer-to-bigint conversion using background migrations.
-
#backfill_iids(table) ⇒ Object
Note this should only be used with very small tables.
-
#change_column_type_concurrently(table, column, new_type, type_cast_function: nil, batch_column_name: :id) ⇒ Object
Changes the type of a column concurrently.
- #check_trigger_permissions!(table) ⇒ Object
-
#cleanup_concurrent_column_rename(table, old, new) ⇒ Object
Cleans up a concurrent column name.
-
#cleanup_concurrent_column_type_change(table, column, temp_column: nil) ⇒ Object
Performs cleanup of a concurrent type change.
-
#column_for(table, name) ⇒ Object
Returns the column for the given table and column name.
-
#concurrent_foreign_key_name(table, column, prefix: 'fk_') ⇒ Object
Returns the name for a concurrent foreign key.
- #convert_to_bigint_column(column) ⇒ Object
- #convert_to_type_column(column, from_type, to_type) ⇒ Object
-
#copy_foreign_keys(table, old, new) ⇒ Object
Copies all foreign keys for the old column to the new column.
-
#copy_indexes(table, old, new) ⇒ Object
Copies all indexes for the old column to a new column.
- #create_or_update_plan_limit(limit_name, plan_name, limit_value) ⇒ Object
-
#create_temporary_columns_and_triggers(table, mappings, primary_key: :id, old_bigint_column_naming: false) ⇒ Object
rubocop:disable Metrics/CyclomaticComplexity,Metrics/PerceivedComplexity.
- #define_batchable_model(table_name, connection: self.connection, primary_key: nil) ⇒ Object
- #drop_sequence(table_name, column_name, sequence_name) ⇒ Object
- #each_batch(table_name, connection: self.connection, **kwargs) ⇒ Object
- #each_batch_range(table_name, connection: self.connection, **kwargs) ⇒ Object
- #false_value ⇒ Object
- #foreign_key_exists?(source, target = nil, **options) ⇒ Boolean
-
#foreign_keys_for(table, column) ⇒ Object
Returns an Array containing the foreign keys for the given column.
-
#index_exists_by_name?(table, index) ⇒ Boolean
Fetches indexes on a column by name for postgres.
- #index_invalid?(index_name, schema: nil) ⇒ Boolean
-
#indexes_for(table, column) ⇒ Object
Returns an Array containing the indexes for the given column.
-
#initialize_conversion_of_integer_to_bigint(table, columns, primary_key: :id) ⇒ Object
Initializes the conversion of a set of integer columns to bigint.
-
#install_rename_triggers(table, old, new, trigger_name: nil) ⇒ Object
Installs triggers in a table that keep a new column in sync with an old one.
-
#install_sharding_key_assignment_trigger(**args) ⇒ Object
Installs a trigger in a table that assigns a sharding key from an associated table.
- #lock_tables(*tables, mode: :access_exclusive, only: nil, nowait: nil) ⇒ Object
- #postgres_exists_by_name?(table, name) ⇒ Boolean
-
#remove_column_default(table_name, column_name) ⇒ Object
While it is safe to call ‘change_column_default` on a column without default it would still require access exclusive lock on the table and for tables with high autovacuum(wraparound prevention) it will fail if their executions overlap.
-
#remove_concurrent_index(table_name, column_name, options = {}) ⇒ Object
Removes an existed index, concurrently.
-
#remove_concurrent_index_by_name(table_name, index_name, options = {}) ⇒ Object
Removes an existing index, concurrently.
- #remove_foreign_key_if_exists(source, target = nil, **kwargs) ⇒ Object
- #remove_foreign_key_without_error(*args, **kwargs) ⇒ Object
-
#remove_rename_triggers(table, trigger) ⇒ Object
Removes the triggers used for renaming a column concurrently.
-
#remove_sharding_key_assignment_trigger(**args) ⇒ Object
Removes trigger used for assigning sharding keys.
-
#remove_timestamps(table_name, options = {}) ⇒ Object
To be used in the ‘#down` method of migrations that use `#add_timestamps_with_timezone`.
-
#rename_column_concurrently(table, old, new, type: nil, type_cast_function: nil, batch_column_name: :id) ⇒ Object
Renames a column without requiring downtime.
-
#rename_trigger_name(table, old, new) ⇒ Object
Returns the (base) name to use for triggers when renaming columns.
-
#replace_sql(column, pattern, replacement) ⇒ Object
This will replace the first occurrence of a string in a column with the replacement using ‘regexp_replace`.
-
#restore_conversion_of_integer_to_bigint(table, columns, primary_key: :id) ⇒ Object
Reverts ‘cleanup_conversion_of_integer_to_bigint`.
-
#revert_backfill_conversion_of_integer_to_bigint(table, columns, primary_key: :id) ⇒ Object
Reverts ‘backfill_conversion_of_integer_to_bigint`.
-
#revert_initialize_conversion_of_integer_to_bigint(table, columns) ⇒ Object
(also: #cleanup_conversion_of_integer_to_bigint)
Reverts ‘initialize_conversion_of_integer_to_bigint`.
- #swap_primary_key(table_name, primary_key_name, index_to_use) ⇒ Object (also: #unswap_primary_key)
- #table_integer_ids ⇒ Object
- #true_value ⇒ Object
-
#undo_change_column_type_concurrently(table, column) ⇒ Object
Reverses operations performed by change_column_type_concurrently.
-
#undo_cleanup_concurrent_column_rename(table, old, new, type: nil, batch_column_name: :id) ⇒ Object
Reverses the operations performed by cleanup_concurrent_column_rename.
-
#undo_cleanup_concurrent_column_type_change(table, column, old_type, type_cast_function: nil, batch_column_name: :id, limit: nil, temp_column: nil) ⇒ Object
Reverses operations performed by cleanup_concurrent_column_type_change.
-
#undo_rename_column_concurrently(table, old, new) ⇒ Object
Reverses operations performed by rename_column_concurrently.
-
#update_column_in_batches(table_name, column, value, batch_size: nil, batch_column_name: :id, disable_lock_writes: false) ⇒ Object
Updates the value of a column in batches.
- #validate_foreign_key(source, column, name: nil) ⇒ Object
Methods included from PartitionHelpers
#partition?, #table_partitioned?
Methods included from WraparoundVacuumHelpers
#check_if_wraparound_in_progress
Methods included from AsyncConstraints::MigrationHelpers
#prepare_async_check_constraint_validation, #prepare_async_foreign_key_validation, #prepare_partitioned_async_check_constraint_validation, #prepare_partitioned_async_foreign_key_validation, #unprepare_async_check_constraint_validation, #unprepare_async_foreign_key_validation, #unprepare_partitioned_async_check_constraint_validation, #unprepare_partitioned_async_foreign_key_validation
Methods included from AsyncIndexes::MigrationHelpers
#async_index_creation_available?, #prepare_async_index, #prepare_async_index_from_sql, #prepare_async_index_removal, #unprepare_async_index, #unprepare_async_index_by_name
Methods included from RenameTableHelpers
#finalize_table_rename, #rename_table_safely, #undo_finalize_table_rename, #undo_rename_table_safely
Methods included from Gitlab::Database::Migrations::RedisHelpers
Methods included from Gitlab::Database::Migrations::SidekiqHelpers
#migrate_across_instance, #migrate_within_instance, #sidekiq_queue_migrate, #sidekiq_remove_jobs
Methods included from Gitlab::Database::Migrations::ExtensionHelpers
#create_extension, #drop_extension
Methods included from Gitlab::Database::Migrations::ConstraintsHelpers
#add_check_constraint, #add_multi_column_not_null_constraint, #add_not_null_constraint, #add_text_limit, #check_constraint_exists?, check_constraint_exists?, #check_constraint_name, #check_not_null_constraint_exists?, #check_text_limit_exists?, #copy_check_constraints, #drop_constraint, #remove_check_constraint, #remove_multi_column_not_null_constraint, #remove_not_null_constraint, #remove_text_limit, #rename_constraint, #switch_constraint_names, #text_limit_name, #validate_check_constraint, #validate_multi_column_not_null_constraint, #validate_not_null_constraint, #validate_text_limit
Methods included from Gitlab::Database::Migrations::TimeoutHelpers
Methods included from Gitlab::Database::Migrations::LockRetriesHelpers
Methods included from Gitlab::Database::Migrations::BatchedBackgroundMigrationHelpers
#delete_batched_background_migration, #ensure_batched_background_migration_is_finished, #finalize_batched_background_migration, #gitlab_schema_from_context, #queue_batched_background_migration
Methods included from Gitlab::Database::Migrations::BackgroundMigrationHelpers
#delete_job_tracking, #delete_queued_jobs, #finalize_background_migration, #migrate_in, #queue_background_migration_jobs_by_range_at_intervals, #requeue_background_migration_jobs_by_range_at_intervals
Methods included from Gitlab::Database::Migrations::ReestablishedConnectionStack
#with_restored_connection_stack
Instance Method Details
#add_concurrent_foreign_key(source, target, column:, **options) ⇒ Object
Adds a foreign key with only minimal locking on the tables involved.
This method only requires minimal locking
source - The source table containing the foreign key. target - The target table the key points to. column - The name of the column to create the foreign key on. target_column - The name of the referenced column, defaults to “id”. on_delete - The action to perform when associated data is removed,
defaults to "CASCADE".
on_update - The action to perform when associated data is updated,
defaults to nil. This is useful for multi column FKs if
it's desirable to update one of the columns.
name - The name of the foreign key. validate - Flag that controls whether the new foreign key will be validated after creation.
If the flag is not set, the constraint will only be enforced for new data.
reverse_lock_order - Flag that controls whether we should attempt to acquire locks in the reverse
order of the ALTER TABLE. This can be useful in situations where the foreign
key creation could deadlock with another process.
256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 |
# File 'lib/gitlab/database/migration_helpers.rb', line 256 def add_concurrent_foreign_key(source, target, column:, **) .reverse_merge!({ on_delete: :cascade, on_update: nil, target_column: :id, validate: true, reverse_lock_order: false, allow_partitioned: false, column: column }) # Transactions would result in ALTER TABLE locks being held for the # duration of the transaction, defeating the purpose of this method. if transaction_open? raise 'add_concurrent_foreign_key can not be run inside a transaction' end if !.delete(:allow_partitioned) && table_partitioned?(source) raise ArgumentError, 'add_concurrent_foreign_key can not be used on a partitioned ' \ 'table. Please use add_concurrent_partitioned_foreign_key on the partitioned table ' \ 'as we need to create foreign keys on each partition and a FK on the parent table' end [:name] ||= concurrent_foreign_key_name(source, column) [:primary_key] = [:target_column] = .slice(:column, :on_delete, :on_update, :name, :primary_key) if foreign_key_exists?(source, target, **) = "Foreign key not created because it exists already " \ "(this may be due to an aborted migration or similar): " \ "source: #{source}, target: #{target}, column: #{[:column]}, "\ "name: #{[:name]}, on_update: #{[:on_update]}, "\ "on_delete: #{[:on_delete]}" Gitlab::AppLogger.warn else execute_add_concurrent_foreign_key(source, target, ) end # Validate the existing constraint. This can potentially take a very # long time to complete, but fortunately does not lock the source table # while running. # Disable this check by passing `validate: false` to the method call # The check will be enforced for new data (inserts) coming in, # but validating existing data is delayed. # # Note this is a no-op in case the constraint is VALID already if [:validate] begin disable_statement_timeout do execute("ALTER TABLE #{source} VALIDATE CONSTRAINT #{[:name]};") end rescue PG::ForeignKeyViolation => e with_lock_retries do execute("ALTER TABLE #{source} DROP CONSTRAINT #{[:name]};") end raise "Migration failed intentionally due to ForeignKeyViolation: #{e.}" end end end |
#add_concurrent_index(table_name, column_name, options = {}) ⇒ Object
Creates a new index, concurrently
Example:
add_concurrent_index :users, :some_column
See Rails’ ‘add_index` for more info on the available arguments.
107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 |
# File 'lib/gitlab/database/migration_helpers.rb', line 107 def add_concurrent_index(table_name, column_name, = {}) if transaction_open? raise 'add_concurrent_index can not be run inside a transaction, ' \ 'you can disable transactions by calling disable_ddl_transaction! ' \ 'in the body of your migration class' end if !.delete(:allow_partition) && partition?(table_name) raise ArgumentError, 'add_concurrent_index can not be used on a partitioned ' \ 'table. Please use add_concurrent_partitioned_index on the partitioned table ' \ 'as we need to create indexes on each partition and an index on the parent table' end = .merge({ algorithm: :concurrently }) if index_exists?(table_name, column_name, **) name = [:name] || index_name(table_name, column_name) _, schema = table_name.to_s.split('.').reverse if index_invalid?(name, schema: schema) say "Index being recreated because the existing version was INVALID: table_name: #{table_name}, column_name: #{column_name}" remove_concurrent_index_by_name(table_name, name) else say "Index not created because it already exists (this may be due to an aborted migration or similar): table_name: #{table_name}, column_name: #{column_name}" return end end disable_statement_timeout do add_index(table_name, column_name, **) end # We created this index. Now let's remove the queuing entry for async creation in case it's still there. unprepare_async_index(table_name, column_name, **) end |
#add_primary_key_using_index(table_name, pk_name, index_to_use) ⇒ Object
1144 1145 1146 1147 1148 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1144 def add_primary_key_using_index(table_name, pk_name, index_to_use) execute <<~SQL ALTER TABLE #{quote_table_name(table_name)} ADD CONSTRAINT #{quote_table_name(pk_name)} PRIMARY KEY USING INDEX #{quote_table_name(index_to_use)} SQL end |
#add_sequence(table_name, column_name, sequence_name, start_value) ⇒ Object
1165 1166 1167 1168 1169 1170 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1165 def add_sequence(table_name, column_name, sequence_name, start_value) execute <<~SQL CREATE SEQUENCE #{quote_table_name(sequence_name)} START #{start_value}; ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT nextval(#{quote(sequence_name)}) SQL end |
#add_timestamps_with_timezone(table_name, options = {}) ⇒ Object
Adds ‘created_at` and `updated_at` columns with timezone information.
This method is an improved version of Rails’ built-in method ‘add_timestamps`.
By default, adds ‘created_at` and `updated_at` columns, but these can be specified as:
(:my_table, columns: [:created_at, :deleted_at])
This allows you to create just the timestamps you need, saving space.
Available options are:
:default - The default value for the column.
:null - When set to `true` the column will allow NULL values.
The default is to not allow NULL values.
:columns - the column names to create. Must end with `_at`.
Default value: `DEFAULT_TIMESTAMP_COLUMNS`
All options are optional.
69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
# File 'lib/gitlab/database/migration_helpers.rb', line 69 def (table_name, = {}) columns = .fetch(:columns, DEFAULT_TIMESTAMP_COLUMNS) columns.each do |column_name| (column_name) add_column( table_name, column_name, :datetime_with_timezone, default: [:default], null: [:null] || false ) end end |
#backfill_conversion_of_integer_to_bigint(table, columns, primary_key: :id, batch_size: 20_000, sub_batch_size: 1000, pause_ms: 100, interval: 2.minutes) ⇒ Object
Backfills the new columns used in an integer-to-bigint conversion using background migrations.
-
This helper should be called from a post-deployment migration.
-
In order for this helper to work properly, the new columns must be first initialized with the ‘initialize_conversion_of_integer_to_bigint` helper.
-
It tracks the scheduled background jobs through Gitlab::Database::BackgroundMigration::BatchedMigration, which allows a more thorough check that all jobs succeeded in the cleanup migration and is way faster for very large tables.
Note: this helper is intended to be used in a post-deployment migration, to ensure any new code is deployed (including background job changes) before we begin processing the background migration.
This helper is part 2 of a multi-step migration process:
-
initialize_conversion_of_integer_to_bigint to create the new columns and database trigger
-
backfill_conversion_of_integer_to_bigint to copy historic data using background migrations
-
remaining steps TBD, see #288005
-
table - The name of the database table containing the column columns - The name, or an array of names, of the column(s) we want to convert to bigint. primary_key - The name of the primary key column (most often :id) batch_size - The number of rows to schedule in a single background migration sub_batch_size - The smaller batches that will be used by each scheduled job
to update the table. Useful to keep each update at ~100ms while executing
more updates per interval (2.minutes)
Note that each execution of a sub-batch adds a constant 100ms sleep
time in between the updates, which must be taken into account
while calculating the batch, sub_batch and interval values.
interval - The time interval between every background migration
example: Assume that we have figured out that updating 200 records of the events
table takes ~100ms on average.
We can set the sub_batch_size to 200, leave the interval to the default
and set the batch_size to 50_000 which will require
~50s = (50000 / 200) * (0.1 + 0.1) to complete and leaves breathing space
between the scheduled jobs
884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 |
# File 'lib/gitlab/database/migration_helpers.rb', line 884 def backfill_conversion_of_integer_to_bigint( table, columns, primary_key: :id, batch_size: 20_000, sub_batch_size: 1000, pause_ms: 100, interval: 2.minutes ) unless table_exists?(table) raise "Table #{table} does not exist" end unless column_exists?(table, primary_key) raise "Column #{primary_key} does not exist on #{table}" end conversions = Array.wrap(columns).to_h do |column| raise ArgumentError, "Column #{column} does not exist on #{table}" unless column_exists?(table, column) temporary_name = convert_to_bigint_column(column) raise ArgumentError, "Column #{temporary_name} does not exist on #{table}" unless column_exists?(table, temporary_name) [column, temporary_name] end queue_batched_background_migration( 'CopyColumnUsingBackgroundMigrationJob', table, primary_key, conversions.keys, conversions.values, job_interval: interval, pause_ms: pause_ms, batch_size: batch_size, sub_batch_size: sub_batch_size) end |
#backfill_iids(table) ⇒ Object
Note this should only be used with very small tables
1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1131 def backfill_iids(table) sql = <<-END UPDATE #{table} SET iid = #{table}_with_calculated_iid.iid_num FROM ( SELECT id, ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY id ASC) AS iid_num FROM #{table} ) AS #{table}_with_calculated_iid WHERE #{table}.id = #{table}_with_calculated_iid.id END execute(sql) end |
#change_column_type_concurrently(table, column, new_type, type_cast_function: nil, batch_column_name: :id) ⇒ Object
Changes the type of a column concurrently.
table - The table containing the column. column - The name of the column to change. new_type - The new column type.
594 595 596 597 598 |
# File 'lib/gitlab/database/migration_helpers.rb', line 594 def change_column_type_concurrently(table, column, new_type, type_cast_function: nil, batch_column_name: :id) temp_column = "#{column}_for_type_change" rename_column_concurrently(table, column, temp_column, type: new_type, type_cast_function: type_cast_function, batch_column_name: batch_column_name) end |
#check_trigger_permissions!(table) ⇒ Object
1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1066 def (table) unless Grant.create_and_execute_trigger?(table) dbname = ApplicationRecord.database.database_name user = ApplicationRecord.database.username raise <<-EOF Your database user is not allowed to create, drop, or execute triggers on the table #{table}. If you are using PostgreSQL you can solve this by logging in to the GitLab database (#{dbname}) using a super user and running: ALTER #{user} WITH SUPERUSER This query will grant the user super user permissions, ensuring you don't run into similar problems in the future (e.g. when new tables are created). EOF end end |
#cleanup_concurrent_column_rename(table, old, new) ⇒ Object
Cleans up a concurrent column name.
This method takes care of removing previously installed triggers as well as removing the old column.
table - The name of the database table. old - The name of the old column. new - The name of the new column.
696 697 698 699 700 701 702 703 704 |
# File 'lib/gitlab/database/migration_helpers.rb', line 696 def cleanup_concurrent_column_rename(table, old, new) trigger_name = rename_trigger_name(table, old, new) (table) remove_rename_triggers(table, trigger_name) remove_column(table, old) end |
#cleanup_concurrent_column_type_change(table, column, temp_column: nil) ⇒ Object
Performs cleanup of a concurrent type change.
table - The table containing the column. column - The name of the column to change. new_type - The new column type.
615 616 617 618 619 620 621 622 623 624 |
# File 'lib/gitlab/database/migration_helpers.rb', line 615 def cleanup_concurrent_column_type_change(table, column, temp_column: nil) temp_column ||= "#{column}_for_type_change" transaction do # This has to be performed in a transaction as otherwise we might have # inconsistent data. cleanup_concurrent_column_rename(table, column, temp_column) rename_column(table, temp_column, column) end end |
#column_for(table, name) ⇒ Object
Returns the column for the given table and column name.
1024 1025 1026 1027 1028 1029 1030 1031 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1024 def column_for(table, name) name = name.to_s column = columns(table).find { |column| column.name == name } raise((table, "column", name)) if column.nil? column end |
#concurrent_foreign_key_name(table, column, prefix: 'fk_') ⇒ Object
Returns the name for a concurrent foreign key.
PostgreSQL constraint names have a limit of 63 bytes. The logic used here is based on Rails’ foreign_key_name() method, which unfortunately is private so we can’t rely on it directly.
prefix:
-
The default prefix is ‘fk_` for backward compatibility with the existing
concurrent foreign key helpers.
-
For standard rails foreign keys the prefix is ‘fk_rails_`
373 374 375 376 377 378 |
# File 'lib/gitlab/database/migration_helpers.rb', line 373 def concurrent_foreign_key_name(table, column, prefix: 'fk_') identifier = "#{table}_#{multiple_columns(column, separator: '_')}_fk" hashed_identifier = Digest::SHA256.hexdigest(identifier).first(10) "#{prefix}#{hashed_identifier}" end |
#convert_to_bigint_column(column) ⇒ Object
740 741 742 |
# File 'lib/gitlab/database/migration_helpers.rb', line 740 def convert_to_bigint_column(column) "#{column}_convert_to_bigint" end |
#convert_to_type_column(column, from_type, to_type) ⇒ Object
736 737 738 |
# File 'lib/gitlab/database/migration_helpers.rb', line 736 def convert_to_type_column(column, from_type, to_type) "#{column}_convert_#{from_type}_to_#{to_type}" end |
#copy_foreign_keys(table, old, new) ⇒ Object
Copies all foreign keys for the old column to the new column.
table - The table containing the columns and indexes. old - The old column. new - The new column.
1014 1015 1016 1017 1018 1019 1020 1021 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1014 def copy_foreign_keys(table, old, new) foreign_keys_for(table, old).each do |fk| add_concurrent_foreign_key(fk.from_table, fk.to_table, column: new, on_delete: fk.on_delete) end end |
#copy_indexes(table, old, new) ⇒ Object
Copies all indexes for the old column to a new column.
table - The table containing the columns and indexes. old - The old column. new - The new column.
962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 |
# File 'lib/gitlab/database/migration_helpers.rb', line 962 def copy_indexes(table, old, new) old = old.to_s new = new.to_s indexes_for(table, old).each do |index| new_columns = index.columns.map do |column| column == old ? new : column end # This is necessary as we can't properly rename indexes such as # "ci_taggings_idx". unless index.name.include?(old) raise "The index #{index.name} can not be copied as it does not "\ "mention the old column. You have to rename this index manually first." end name = index.name.gsub(old, new) if name.length > 63 digest = Digest::SHA256.hexdigest(name).first(10) name = "idx_copy_#{digest}" end = { unique: index.unique, name: name, length: index.lengths, order: index.orders } [:using] = index.using if index.using [:where] = index.where if index.where unless index.opclasses.blank? opclasses = index.opclasses.dup # Copy the operator classes for the old column (if any) to the new # column. opclasses[new] = opclasses.delete(old) if opclasses[old] [:opclass] = opclasses end add_concurrent_index(table, new_columns, ) end end |
#create_or_update_plan_limit(limit_name, plan_name, limit_value) ⇒ Object
1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1118 def create_or_update_plan_limit(limit_name, plan_name, limit_value) limit_name_quoted = quote_column_name(limit_name) plan_name_quoted = quote(plan_name) limit_value_quoted = quote(limit_value) execute <<~SQL INSERT INTO plan_limits (plan_id, #{limit_name_quoted}) SELECT id, #{limit_value_quoted} FROM plans WHERE name = #{plan_name_quoted} LIMIT 1 ON CONFLICT (plan_id) DO UPDATE SET #{limit_name_quoted} = EXCLUDED.#{limit_name_quoted}; SQL end |
#create_temporary_columns_and_triggers(table, mappings, primary_key: :id, old_bigint_column_naming: false) ⇒ Object
rubocop:disable Metrics/CyclomaticComplexity,Metrics/PerceivedComplexity
1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1173 def create_temporary_columns_and_triggers(table, mappings, primary_key: :id, old_bigint_column_naming: false) raise ArgumentError, "No mappings for column conversion provided" if mappings.blank? unless mappings.values.all? { |values| mapping_has_required_columns?(values) } raise ArgumentError, "Some mappings don't have required keys provided" end neutral_values_for_type = { int: 0, bigint: 0, uuid: '00000000-0000-0000-0000-000000000000' } unless table_exists?(table) raise "Table #{table} does not exist" end unless column_exists?(table, primary_key) raise "Column #{primary_key} does not exist on #{table}" end columns = mappings.keys columns.each do |column| next if column_exists?(table, column) raise ArgumentError, "Column #{column} does not exist on #{table}" end (table) if old_bigint_column_naming mappings.each do |column, params| params.merge!( temporary_column_name: convert_to_bigint_column(column) ) end else mappings.each do |column, params| params.merge!( temporary_column_name: convert_to_type_column(column, params[:from_type], params[:to_type]) ) end end with_lock_retries do mappings.each do |(column_name, params)| column = column_for(table, column_name) temporary_name = params[:temporary_column_name] data_type = params[:to_type] default_value = params[:default_value] if (column.name.to_s == primary_key.to_s) || !column.null # If the column to be converted is either a PK or is defined as NOT NULL, # set it to `NOT NULL DEFAULT 0` and we'll copy paste the correct values bellow # That way, we skip the expensive validation step required to add # a NOT NULL constraint at the end of the process add_column( table, temporary_name, data_type, default: column.default || default_value || neutral_values_for_type.fetch(data_type), null: false ) else add_column( table, temporary_name, data_type, default: column.default ) end end old_column_names = mappings.keys temporary_column_names = mappings.values.map { |v| v[:temporary_column_name] } install_rename_triggers(table, old_column_names, temporary_column_names) end end |
#define_batchable_model(table_name, connection: self.connection, primary_key: nil) ⇒ Object
39 40 41 |
# File 'lib/gitlab/database/migration_helpers.rb', line 39 def define_batchable_model(table_name, connection: self.connection, primary_key: nil) super(table_name, connection: connection, primary_key: primary_key) end |
#drop_sequence(table_name, column_name, sequence_name) ⇒ Object
1158 1159 1160 1161 1162 1163 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1158 def drop_sequence(table_name, column_name, sequence_name) execute <<~SQL ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} DROP DEFAULT; DROP SEQUENCE IF EXISTS #{quote_table_name(sequence_name)} SQL end |
#each_batch(table_name, connection: self.connection, **kwargs) ⇒ Object
43 44 45 |
# File 'lib/gitlab/database/migration_helpers.rb', line 43 def each_batch(table_name, connection: self.connection, **kwargs) super(table_name, connection: connection, **kwargs) end |
#each_batch_range(table_name, connection: self.connection, **kwargs) ⇒ Object
47 48 49 |
# File 'lib/gitlab/database/migration_helpers.rb', line 47 def each_batch_range(table_name, connection: self.connection, **kwargs) super(table_name, connection: connection, **kwargs) end |
#false_value ⇒ Object
384 385 386 |
# File 'lib/gitlab/database/migration_helpers.rb', line 384 def false_value Database.false_value end |
#foreign_key_exists?(source, target = nil, **options) ⇒ Boolean
330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 |
# File 'lib/gitlab/database/migration_helpers.rb', line 330 def foreign_key_exists?(source, target = nil, **) # This if block is necessary because foreign_key_exists? is called in down migrations that may execute before # the postgres_foreign_keys view had necessary columns added. # In that case, we revert to the previous behavior of this method. # The behavior in the if block has a bug: it always returns false if the fk being checked has multiple columns. # This can be removed after init_schema.rb passes 20221122210711_add_columns_to_postgres_foreign_keys.rb # Tracking issue: https://gitlab.com/gitlab-org/gitlab/-/issues/386796 unless connection.column_exists?('postgres_foreign_keys', 'constrained_table_name') return foreign_keys(source).any? do |foreign_key| tables_match?(target.to_s, foreign_key.to_table.to_s) && (foreign_key., ) end end # Since we may be migrating in one go from a previous version without # `constrained_table_name` then we may see that this column exists # (as above) but the schema cache is still outdated for the model. unless Gitlab::Database::PostgresForeignKey.column_names.include?('constrained_table_name') Gitlab::Database::PostgresForeignKey.reset_column_information end fks = Gitlab::Database::PostgresForeignKey.by_constrained_table_name_or_identifier(source) fks = fks.by_referenced_table_name(target) if target fks = fks.by_name([:name]) if [:name] fks = fks.by_constrained_columns([:column]) if [:column] fks = fks.by_referenced_columns([:primary_key]) if [:primary_key] fks = fks.by_on_delete_action([:on_delete]) if [:on_delete] fks.exists? end |
#foreign_keys_for(table, column) ⇒ Object
Returns an Array containing the foreign keys for the given column.
951 952 953 954 955 |
# File 'lib/gitlab/database/migration_helpers.rb', line 951 def foreign_keys_for(table, column) column = column.to_s foreign_keys(table).select { |fk| fk.column == column } end |
#index_exists_by_name?(table, index) ⇒ Boolean
Fetches indexes on a column by name for postgres.
This will include indexes using an expression on the column, for example: ‘CREATE INDEX CONCURRENTLY index_name ON table (LOWER(column));`
We can remove this when upgrading to Rails 5 with an updated ‘index_exists?`:
Or this can be removed when we no longer support postgres < 9.5, so we can use ‘CREATE INDEX IF NOT EXISTS`.
1096 1097 1098 1099 1100 1101 1102 1103 1104 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1096 def index_exists_by_name?(table, index) # We can't fall back to the normal `index_exists?` method because that # does not find indexes without passing a column name. if indexes(table).map(&:name).include?(index.to_s) true else postgres_exists_by_name?(table, index) end end |
#index_invalid?(index_name, schema: nil) ⇒ Boolean
145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 |
# File 'lib/gitlab/database/migration_helpers.rb', line 145 def index_invalid?(index_name, schema: nil) index_name = connection.quote(index_name) schema = connection.quote(schema) if schema schema ||= 'current_schema()' connection.select_value(<<~SQL) select not i.indisvalid from pg_class c inner join pg_index i on c.oid = i.indexrelid inner join pg_namespace n on n.oid = c.relnamespace where n.nspname = #{schema} and c.relname = #{index_name} SQL end |
#indexes_for(table, column) ⇒ Object
Returns an Array containing the indexes for the given column
944 945 946 947 948 |
# File 'lib/gitlab/database/migration_helpers.rb', line 944 def indexes_for(table, column) column = column.to_s indexes(table).select { |index| index.columns.include?(column) } end |
#initialize_conversion_of_integer_to_bigint(table, columns, primary_key: :id) ⇒ Object
Initializes the conversion of a set of integer columns to bigint
It can be used for converting both a Primary Key and any Foreign Keys that may reference it or any other integer column that we may want to upgrade (e.g. columns that store IDs, but are not set as FKs).
-
For primary keys and Foreign Keys (or other columns) defined as NOT NULL,
the new bigint column is added with a hardcoded NOT NULL DEFAULT 0 which allows us to skip a very costly verification step once we are ready to switch it.
This is crucial for Primary Key conversions, because setting a column
as the PK converts even check constraints to NOT NULL constraints and forces an inline re-verification of the whole table.
-
It sets up a trigger to keep the two columns in sync.
Note: this helper is intended to be used in a regular (pre-deployment) migration.
This helper is part 1 of a multi-step migration process:
-
initialize_conversion_of_integer_to_bigint to create the new columns and database trigger
-
backfill_conversion_of_integer_to_bigint to copy historic data using background migrations
-
remaining steps TBD, see #288005
-
table - The name of the database table containing the column columns - The name, or array of names, of the column(s) that we want to convert to bigint. primary_key - The name of the primary key column (most often :id)
769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 |
# File 'lib/gitlab/database/migration_helpers.rb', line 769 def initialize_conversion_of_integer_to_bigint(table, columns, primary_key: :id) integer_ids = table_integer_ids columns = Array(columns) pending_int_ids = Array(integer_ids[table.to_s]) - columns.map(&:to_s) # This check can be removed once we convert all integer IDs to bigint # in https://gitlab.com/gitlab-org/gitlab/-/issues/465805 if can_enforce_initializing_all_int_ids? && pending_int_ids.present? raise format(PENDING_INT_IDS_ERROR_MSG, table: table, int_ids: pending_int_ids) end mappings = columns.map do |c| { c => { from_type: :int, to_type: :bigint, default_value: 0 } } end.reduce(&:merge) create_temporary_columns_and_triggers( table, mappings, primary_key: primary_key, old_bigint_column_naming: true ) deleted = integer_ids.delete(table.to_s) return unless can_enforce_initializing_all_int_ids? && deleted.present? update_table_integer_ids_file(integer_ids) end |
#install_rename_triggers(table, old, new, trigger_name: nil) ⇒ Object
Installs triggers in a table that keep a new column in sync with an old one.
table - The name of the table to install the trigger in. old_column - The name of the old column. new_column - The name of the new column. trigger_name - The name of the trigger to use (optional).
553 554 555 |
# File 'lib/gitlab/database/migration_helpers.rb', line 553 def install_rename_triggers(table, old, new, trigger_name: nil) Gitlab::Database::UnidirectionalCopyTrigger.on_table(table, connection: connection).create(old, new, trigger_name: trigger_name) end |
#install_sharding_key_assignment_trigger(**args) ⇒ Object
Installs a trigger in a table that assigns a sharding key from an associated table.
table: The table to install the trigger in. sharding_key: The column to be assigned on ‘table`. parent_table: The associated table with the sharding key to be copied. parent_sharding_key: The sharding key on the parent table that will be copied to `sharding_key` on `table`. foreign_key: The column used to fetch the relevant record from `parent_table`.
574 575 576 |
# File 'lib/gitlab/database/migration_helpers.rb', line 574 def install_sharding_key_assignment_trigger(**args) Gitlab::Database::Triggers::AssignDesiredShardingKey.new(**args.merge(connection: connection)).create end |
#lock_tables(*tables, mode: :access_exclusive, only: nil, nowait: nil) ⇒ Object
1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1266 def lock_tables(*tables, mode: :access_exclusive, only: nil, nowait: nil) only_param = only && 'ONLY' nowait_param = nowait && 'NOWAIT' tables_param = tables.map { |t| quote_table_name(t) }.join(', ') mode_param = mode.to_s.upcase.tr('_', ' ') execute(<<~SQL.squish) LOCK TABLE #{only_param} #{tables_param} IN #{mode_param} MODE #{nowait_param} SQL end |
#postgres_exists_by_name?(table, name) ⇒ Boolean
1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1106 def postgres_exists_by_name?(table, name) index_sql = <<~SQL SELECT COUNT(*) FROM pg_catalog.pg_indexes WHERE schemaname = #{connection.quote(current_schema)} AND tablename = #{connection.quote(table)} AND indexname = #{connection.quote(name)} SQL connection.select_value(index_sql).to_i > 0 end |
#remove_column_default(table_name, column_name) ⇒ Object
While it is safe to call ‘change_column_default` on a column without default it would still require access exclusive lock on the table and for tables with high autovacuum(wraparound prevention) it will fail if their executions overlap.
1258 1259 1260 1261 1262 1263 1264 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1258 def remove_column_default(table_name, column_name) column = connection.columns(table_name).find { |col| col.name == column_name.to_s } if column.default || column.default_function change_column_default(table_name, column_name, to: nil) end end |
#remove_concurrent_index(table_name, column_name, options = {}) ⇒ Object
Removes an existed index, concurrently
Example:
remove_concurrent_index :users, :some_column
See Rails’ ‘remove_index` for more info on the available arguments.
169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 |
# File 'lib/gitlab/database/migration_helpers.rb', line 169 def remove_concurrent_index(table_name, column_name, = {}) if transaction_open? raise 'remove_concurrent_index can not be run inside a transaction, ' \ 'you can disable transactions by calling disable_ddl_transaction! ' \ 'in the body of your migration class' end if partition?(table_name) raise ArgumentError, 'remove_concurrent_index can not be used on a partitioned ' \ 'table. Please use remove_concurrent_partitioned_index_by_name on the partitioned table ' \ 'as we need to remove the index on the parent table' end = .merge({ algorithm: :concurrently }) unless index_exists?(table_name, column_name, **) Gitlab::AppLogger.warn "Index not removed because it does not exist (this may be due to an aborted migration or similar): table_name: #{table_name}, column_name: #{column_name}" return end disable_statement_timeout do remove_index(table_name, **.merge({ column: column_name })) end # We removed this index. Now let's make sure it's not queued for async creation. unprepare_async_index(table_name, column_name, **) end |
#remove_concurrent_index_by_name(table_name, index_name, options = {}) ⇒ Object
Removes an existing index, concurrently
Example:
remove_concurrent_index :users, "index_X_by_Y"
See Rails’ ‘remove_index` for more info on the available arguments.
204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 |
# File 'lib/gitlab/database/migration_helpers.rb', line 204 def remove_concurrent_index_by_name(table_name, index_name, = {}) if transaction_open? raise 'remove_concurrent_index_by_name can not be run inside a transaction, ' \ 'you can disable transactions by calling disable_ddl_transaction! ' \ 'in the body of your migration class' end if partition?(table_name) raise ArgumentError, 'remove_concurrent_index_by_name can not be used on a partitioned ' \ 'table. Please use remove_concurrent_partitioned_index_by_name on the partitioned table ' \ 'as we need to remove the index on the parent table' end index_name = index_name[:name] if index_name.is_a?(Hash) raise 'remove_concurrent_index_by_name must get an index name as the second argument' if index_name.blank? = .merge({ algorithm: :concurrently }) unless index_exists_by_name?(table_name, index_name) Gitlab::AppLogger.warn "Index not removed because it does not exist (this may be due to an aborted migration or similar): table_name: #{table_name}, index_name: #{index_name}" return end disable_statement_timeout do remove_index(table_name, **.merge({ name: index_name })) end # We removed this index. Now let's make sure it's not queued for async creation. unprepare_async_index_by_name(table_name, index_name, **) end |
#remove_foreign_key_if_exists(source, target = nil, **kwargs) ⇒ Object
1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1046 def remove_foreign_key_if_exists(source, target = nil, **kwargs) reverse_lock_order = kwargs.delete(:reverse_lock_order) return unless foreign_key_exists?(source, target, **kwargs) if target && reverse_lock_order && transaction_open? execute("LOCK TABLE #{target}, #{source} IN ACCESS EXCLUSIVE MODE") end if target remove_foreign_key(source, target, **kwargs) else remove_foreign_key(source, **kwargs) end end |
#remove_foreign_key_without_error(*args, **kwargs) ⇒ Object
1061 1062 1063 1064 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1061 def remove_foreign_key_without_error(*args, **kwargs) remove_foreign_key(*args, **kwargs) rescue ArgumentError end |
#remove_rename_triggers(table, trigger) ⇒ Object
Removes the triggers used for renaming a column concurrently.
558 559 560 |
# File 'lib/gitlab/database/migration_helpers.rb', line 558 def remove_rename_triggers(table, trigger) Gitlab::Database::UnidirectionalCopyTrigger.on_table(table, connection: connection).drop(trigger) end |
#remove_sharding_key_assignment_trigger(**args) ⇒ Object
Removes trigger used for assigning sharding keys.
table: The table to install the trigger in. sharding_key: The column to be assigned on ‘table`. parent_table: The associated table with the sharding key to be copied. parent_sharding_key: The sharding key on the parent table that will be copied to `sharding_key` on `table`. foreign_key: The column used to fetch the relevant record from `parent_table`.
585 586 587 |
# File 'lib/gitlab/database/migration_helpers.rb', line 585 def remove_sharding_key_assignment_trigger(**args) Gitlab::Database::Triggers::AssignDesiredShardingKey.new(**args.merge(connection: connection)).drop end |
#remove_timestamps(table_name, options = {}) ⇒ Object
To be used in the ‘#down` method of migrations that use `#add_timestamps_with_timezone`.
Available options are:
:columns - the column names to remove. Must be one
Default value: `DEFAULT_TIMESTAMP_COLUMNS`
All options are optional.
93 94 95 96 97 98 |
# File 'lib/gitlab/database/migration_helpers.rb', line 93 def (table_name, = {}) columns = .fetch(:columns, DEFAULT_TIMESTAMP_COLUMNS) columns.each do |column_name| remove_column(table_name, column_name) end end |
#rename_column_concurrently(table, old, new, type: nil, type_cast_function: nil, batch_column_name: :id) ⇒ Object
Renames a column without requiring downtime.
Concurrent renames work by using database triggers to ensure both the old and new column are in sync. However, this method will not remove the triggers or the old column automatically; this needs to be done manually in a post-deployment migration. This can be done using the method ‘cleanup_concurrent_column_rename`.
table - The name of the database table containing the column. old - The old column name. new - The new column name. type - The type of the new column. If no type is given the old column’s
type is used.
batch_column_name - option is for tables without primary key, in this
case another unique integer column can be used. Example: :user_id
512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 |
# File 'lib/gitlab/database/migration_helpers.rb', line 512 def rename_column_concurrently(table, old, new, type: nil, type_cast_function: nil, batch_column_name: :id) unless column_exists?(table, batch_column_name) raise "Column #{batch_column_name} does not exist on #{table}" end if transaction_open? raise 'rename_column_concurrently can not be run inside a transaction' end (table) create_column_from(table, old, new, type: type, batch_column_name: batch_column_name, type_cast_function: type_cast_function) install_rename_triggers(table, old, new) end |
#rename_trigger_name(table, old, new) ⇒ Object
Returns the (base) name to use for triggers when renaming columns.
563 564 565 |
# File 'lib/gitlab/database/migration_helpers.rb', line 563 def rename_trigger_name(table, old, new) Gitlab::Database::UnidirectionalCopyTrigger.on_table(table, connection: connection).name(old, new) end |
#replace_sql(column, pattern, replacement) ⇒ Object
This will replace the first occurrence of a string in a column with the replacement using ‘regexp_replace`
1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1035 def replace_sql(column, pattern, replacement) quoted_pattern = Arel::Nodes::Quoted.new(pattern.to_s) quoted_replacement = Arel::Nodes::Quoted.new(replacement.to_s) replace = Arel::Nodes::NamedFunction.new( "regexp_replace", [column, quoted_pattern, quoted_replacement] ) Arel::Nodes::SqlLiteral.new(replace.to_sql) end |
#restore_conversion_of_integer_to_bigint(table, columns, primary_key: :id) ⇒ Object
Reverts ‘cleanup_conversion_of_integer_to_bigint`
table - The name of the database table containing the columns columns - The name, or array of names, of the column(s) that we have converted to bigint. primary_key - The name of the primary key column (most often :id)
829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 |
# File 'lib/gitlab/database/migration_helpers.rb', line 829 def restore_conversion_of_integer_to_bigint(table, columns, primary_key: :id) mappings = Array(columns).map do |c| { c => { from_type: :bigint, to_type: :int, default_value: 0 } } end.reduce(&:merge) create_temporary_columns_and_triggers( table, mappings, primary_key: primary_key, old_bigint_column_naming: true ) end |
#revert_backfill_conversion_of_integer_to_bigint(table, columns, primary_key: :id) ⇒ Object
Reverts ‘backfill_conversion_of_integer_to_bigint`
table - The name of the database table containing the column columns - The name, or an array of names, of the column(s) we want to convert to bigint. primary_key - The name of the primary key column (most often :id)
928 929 930 931 932 933 934 935 936 937 938 939 940 941 |
# File 'lib/gitlab/database/migration_helpers.rb', line 928 def revert_backfill_conversion_of_integer_to_bigint(table, columns, primary_key: :id) columns = Array.wrap(columns) conditions = ActiveRecord::Base.sanitize_sql( [ 'job_class_name = :job_class_name AND table_name = :table_name AND column_name = :column_name AND job_arguments = :job_arguments', { job_class_name: 'CopyColumnUsingBackgroundMigrationJob', table_name: table, column_name: primary_key, job_arguments: [columns, columns.map { |column| convert_to_bigint_column(column) }].to_json } ]) execute("DELETE FROM batched_background_migrations WHERE #{conditions}") end |
#revert_initialize_conversion_of_integer_to_bigint(table, columns) ⇒ Object Also known as: cleanup_conversion_of_integer_to_bigint
Reverts ‘initialize_conversion_of_integer_to_bigint`
table - The name of the database table containing the columns columns - The name, or array of names, of the column(s) that we’re converting to bigint.
807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 |
# File 'lib/gitlab/database/migration_helpers.rb', line 807 def revert_initialize_conversion_of_integer_to_bigint(table, columns) columns = Array.wrap(columns) temporary_columns = columns.map { |column| convert_to_bigint_column(column) } trigger_name = rename_trigger_name(table, columns, temporary_columns) remove_rename_triggers(table, trigger_name) temporary_columns.each { |column| remove_column(table, column, if_exists: true) } return unless can_enforce_initializing_all_int_ids? integer_ids = table_integer_ids integer_ids[table.to_s] = columns.map(&:to_s) update_table_integer_ids_file(integer_ids) end |
#swap_primary_key(table_name, primary_key_name, index_to_use) ⇒ Object Also known as: unswap_primary_key
1150 1151 1152 1153 1154 1155 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1150 def swap_primary_key(table_name, primary_key_name, index_to_use) with_lock_retries(raise_on_exhaustion: true) do drop_constraint(table_name, primary_key_name, cascade: true) add_primary_key_using_index(table_name, primary_key_name, index_to_use) end end |
#table_integer_ids ⇒ Object
1277 1278 1279 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1277 def table_integer_ids YAML.safe_load_file(File.join(INTEGER_IDS_YET_TO_INITIALIZED_TO_BIGINT_FILE_PATH)) end |
#true_value ⇒ Object
380 381 382 |
# File 'lib/gitlab/database/migration_helpers.rb', line 380 def true_value Database.true_value end |
#undo_change_column_type_concurrently(table, column) ⇒ Object
Reverses operations performed by change_column_type_concurrently.
table - The table containing the column. column - The name of the column to change.
604 605 606 607 608 |
# File 'lib/gitlab/database/migration_helpers.rb', line 604 def undo_change_column_type_concurrently(table, column) temp_column = "#{column}_for_type_change" undo_rename_column_concurrently(table, column, temp_column) end |
#undo_cleanup_concurrent_column_rename(table, old, new, type: nil, batch_column_name: :id) ⇒ Object
Reverses the operations performed by cleanup_concurrent_column_rename.
This method adds back the old_column removed by cleanup_concurrent_column_rename. It also adds back the (old_column > new_column) trigger that is removed by cleanup_concurrent_column_rename.
table - The name of the database table containing the column. old - The old column name. new - The new column name. type - The type of the old column. If no type is given the new column’s
type is used.
batch_column_name - option is for tables without primary key, in this
case another unique integer column can be used. Example: :user_id
720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 |
# File 'lib/gitlab/database/migration_helpers.rb', line 720 def undo_cleanup_concurrent_column_rename(table, old, new, type: nil, batch_column_name: :id) unless column_exists?(table, batch_column_name) raise "Column #{batch_column_name} does not exist on #{table}" end if transaction_open? raise 'undo_cleanup_concurrent_column_rename can not be run inside a transaction' end (table) create_column_from(table, new, old, type: type, batch_column_name: batch_column_name) install_rename_triggers(table, old, new) end |
#undo_cleanup_concurrent_column_type_change(table, column, old_type, type_cast_function: nil, batch_column_name: :id, limit: nil, temp_column: nil) ⇒ Object
Reverses operations performed by cleanup_concurrent_column_type_change.
table - The table containing the column. column - The name of the column to change. old_type - The type of the original column used with change_column_type_concurrently. type_cast_function - Required if the conversion back to the original type is not automatic batch_column_name - option for tables without a primary key, in this case
another unique integer column can be used. Example: :user_id
634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 |
# File 'lib/gitlab/database/migration_helpers.rb', line 634 def undo_cleanup_concurrent_column_type_change(table, column, old_type, type_cast_function: nil, batch_column_name: :id, limit: nil, temp_column: nil) Gitlab::Database::QueryAnalyzers::RestrictAllowedSchemas.require_ddl_mode! temp_column ||= "#{column}_for_type_change" # Using a descriptive name that includes orinal column's name risks # taking us above the 63 character limit, so we use a hash identifier = "#{table}_#{column}_for_type_change" hashed_identifier = Digest::SHA256.hexdigest(identifier).first(10) temp_undo_cleanup_column = "tmp_undo_cleanup_column_#{hashed_identifier}" unless column_exists?(table, batch_column_name) raise "Column #{batch_column_name} does not exist on #{table}" end if transaction_open? raise 'undo_cleanup_concurrent_column_type_change can not be run inside a transaction' end (table) begin create_column_from( table, column, temp_undo_cleanup_column, type: old_type, batch_column_name: batch_column_name, type_cast_function: type_cast_function, limit: limit ) transaction do # This has to be performed in a transaction as otherwise we might # have inconsistent data. rename_column(table, column, temp_column) rename_column(table, temp_undo_cleanup_column, column) install_rename_triggers(table, column, temp_column) end rescue StandardError # create_column_from can not run inside a transaction, which means # that there is a risk that if any of the operations that follow it # fail, we'll be left with an inconsistent schema # For those reasons, we make sure that we drop temp_undo_cleanup_column # if an error is caught if column_exists?(table, temp_undo_cleanup_column) remove_column(table, temp_undo_cleanup_column) end raise end end |
#undo_rename_column_concurrently(table, old, new) ⇒ Object
Reverses operations performed by rename_column_concurrently.
This method takes care of removing previously installed triggers as well as removing the new column.
table - The name of the database table. old - The name of the old column. new - The name of the new column.
536 537 538 539 540 541 542 543 544 |
# File 'lib/gitlab/database/migration_helpers.rb', line 536 def undo_rename_column_concurrently(table, old, new) trigger_name = rename_trigger_name(table, old, new) (table) remove_rename_triggers(table, trigger_name) remove_column(table, new) end |
#update_column_in_batches(table_name, column, value, batch_size: nil, batch_column_name: :id, disable_lock_writes: false) ⇒ Object
Updates the value of a column in batches.
This method updates the table in batches of 5% of the total row count. A ‘batch_size` option can also be passed to set this to a fixed number. This method will continue updating rows until no rows remain.
When given a block this method will yield two values to the block:
-
An instance of ‘Arel::Table` for the table that is being updated.
-
The query to run as an Arel object.
By supplying a block one can add extra conditions to the queries being executed. Note that the same block is used for all queries.
Example:
update_column_in_batches(:projects, :foo, 10) do |table, query|
query.where(table[:some_column].eq('hello'))
end
This would result in this method updating only rows where ‘projects.some_column` equals “hello”.
table - The name of the table. column - The name of the column to update. value - The value for the column.
The ‘value` argument is typically a literal. To perform a computed update, an Arel literal can be used instead:
update_value = Arel.sql('bar * baz')
update_column_in_batches(:projects, :foo, update_value) do |table, query|
query.where(table[:some_column].eq('hello'))
end
Rubocop’s Metrics/AbcSize metric is disabled for this method as Rubocop determines this method to be too complex while there’s no way to make it less “complex” without introducing extra methods (which actually will make things more complex).
‘batch_column_name` option is for tables without primary key, in this case another unique integer column can be used. Example: :user_id
rubocop: disable Metrics/AbcSize
433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 |
# File 'lib/gitlab/database/migration_helpers.rb', line 433 def update_column_in_batches(table_name, column, value, batch_size: nil, batch_column_name: :id, disable_lock_writes: false) if transaction_open? raise 'update_column_in_batches can not be run inside a transaction, ' \ 'you can disable transactions by calling disable_ddl_transaction! ' \ 'in the body of your migration class' end table = Arel::Table.new(table_name) count_arel = table.project(Arel.star.count.as('count')) count_arel = yield table, count_arel if block_given? total = exec_query(count_arel.to_sql).to_a.first['count'].to_i return if total == 0 if batch_size.nil? # Update in batches of 5% until we run out of any rows to update. batch_size = ((total / 100.0) * 5.0).ceil max_size = 1000 # The upper limit is 1000 to ensure we don't lock too many rows. For # example, for "merge_requests" even 1% of the table is around 35 000 # rows for GitLab.com. batch_size = max_size if batch_size > max_size end start_arel = table.project(table[batch_column_name]).order(table[batch_column_name].asc).take(1) start_arel = yield table, start_arel if block_given? start_id = exec_query(start_arel.to_sql).to_a.first[batch_column_name.to_s].to_i loop do stop_arel = table.project(table[batch_column_name]) .where(table[batch_column_name].gteq(start_id)) .order(table[batch_column_name].asc) .take(1) .skip(batch_size) stop_arel = yield table, stop_arel if block_given? stop_row = exec_query(stop_arel.to_sql).to_a.first update_arel = Arel::UpdateManager.new .table(table) .set([[table[column], value]]) .where(table[batch_column_name].gteq(start_id)) if stop_row stop_id = stop_row[batch_column_name.to_s].to_i start_id = stop_id update_arel = update_arel.where(table[batch_column_name].lt(stop_id)) end update_arel = yield table, update_arel if block_given? transaction do execute("SELECT set_config('lock_writes.#{table_name}', 'false', true)") if disable_lock_writes execute(update_arel.to_sql) end # There are no more rows left to update. break unless stop_row end end |
#validate_foreign_key(source, column, name: nil) ⇒ Object
318 319 320 321 322 323 324 325 326 327 328 |
# File 'lib/gitlab/database/migration_helpers.rb', line 318 def validate_foreign_key(source, column, name: nil) fk_name = name || concurrent_foreign_key_name(source, column) unless foreign_key_exists?(source, name: fk_name) raise (source, "foreign key", fk_name) end disable_statement_timeout do execute("ALTER TABLE #{source} VALIDATE CONSTRAINT #{fk_name};") end end |