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, RenameTableHelpers
- Included in:
- BackgroundMigration::LegacyUploadsMigrator, CascadingNamespaceSettings, V2, PartitioningMigrationHelpers::IndexHelpers, PartitioningMigrationHelpers::TableManagementHelpers
- Defined in:
- lib/gitlab/database/migration_helpers.rb,
lib/gitlab/database/migration_helpers/v2.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, V2, WraparoundAutovacuum, WraparoundVacuumHelpers
Constant Summary collapse
- 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::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, 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) ⇒ 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) ⇒ 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.
-
#partition?(table_name) ⇒ Boolean
rubocop:enable Metrics/CyclomaticComplexity,Metrics/PerceivedComplexity.
- #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_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_partitioned?(table_name) ⇒ Boolean
- #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) ⇒ 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 WraparoundVacuumHelpers
#check_if_wraparound_in_progress
Methods included from AsyncConstraints::MigrationHelpers
#prepare_async_check_constraint_validation, #prepare_async_foreign_key_validation, #prepare_partitioned_async_foreign_key_validation, #unprepare_async_check_constraint_validation, #unprepare_async_foreign_key_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
#sidekiq_queue_length, #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_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_not_null_constraint, #remove_text_limit, #rename_constraint, #switch_constraint_names, #text_limit_name, #validate_check_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.
240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 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 |
# File 'lib/gitlab/database/migration_helpers.rb', line 240 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] disable_statement_timeout do execute("ALTER TABLE #{source} VALIDATE CONSTRAINT #{[:name]};") 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.
91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 |
# File 'lib/gitlab/database/migration_helpers.rb', line 91 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
1071 1072 1073 1074 1075 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1071 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
1092 1093 1094 1095 1096 1097 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1092 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.
53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
# File 'lib/gitlab/database/migration_helpers.rb', line 53 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, 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
818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 |
# File 'lib/gitlab/database/migration_helpers.rb', line 818 def backfill_conversion_of_integer_to_bigint( table, columns, primary_key: :id, batch_size: 20_000, sub_batch_size: 1000, 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, 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
1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1058 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.
549 550 551 552 553 |
# File 'lib/gitlab/database/migration_helpers.rb', line 549 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
993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 |
# File 'lib/gitlab/database/migration_helpers.rb', line 993 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.
651 652 653 654 655 656 657 658 659 |
# File 'lib/gitlab/database/migration_helpers.rb', line 651 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) ⇒ 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.
570 571 572 573 574 575 576 577 578 579 |
# File 'lib/gitlab/database/migration_helpers.rb', line 570 def cleanup_concurrent_column_type_change(table, column) 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.
951 952 953 954 955 956 957 958 |
# File 'lib/gitlab/database/migration_helpers.rb', line 951 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_`
350 351 352 353 354 355 |
# File 'lib/gitlab/database/migration_helpers.rb', line 350 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
695 696 697 |
# File 'lib/gitlab/database/migration_helpers.rb', line 695 def convert_to_bigint_column(column) "#{column}_convert_to_bigint" end |
#convert_to_type_column(column, from_type, to_type) ⇒ Object
691 692 693 |
# File 'lib/gitlab/database/migration_helpers.rb', line 691 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.
941 942 943 944 945 946 947 948 |
# File 'lib/gitlab/database/migration_helpers.rb', line 941 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.
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 922 923 924 925 926 927 928 929 930 931 932 933 934 |
# File 'lib/gitlab/database/migration_helpers.rb', line 894 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) = { 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
1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1045 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
1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1100 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) ⇒ Object
21 22 23 |
# File 'lib/gitlab/database/migration_helpers.rb', line 21 def define_batchable_model(table_name, connection: self.connection) super(table_name, connection: connection) end |
#drop_sequence(table_name, column_name, sequence_name) ⇒ Object
1085 1086 1087 1088 1089 1090 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1085 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
25 26 27 |
# File 'lib/gitlab/database/migration_helpers.rb', line 25 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
29 30 31 |
# File 'lib/gitlab/database/migration_helpers.rb', line 29 def each_batch_range(table_name, connection: self.connection, **kwargs) super(table_name, connection: connection, **kwargs) end |
#false_value ⇒ Object
361 362 363 |
# File 'lib/gitlab/database/migration_helpers.rb', line 361 def false_value Database.false_value end |
#foreign_key_exists?(source, target = nil, **options) ⇒ Boolean
307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 |
# File 'lib/gitlab/database/migration_helpers.rb', line 307 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.
883 884 885 886 887 |
# File 'lib/gitlab/database/migration_helpers.rb', line 883 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`.
1023 1024 1025 1026 1027 1028 1029 1030 1031 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1023 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
129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 |
# File 'lib/gitlab/database/migration_helpers.rb', line 129 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
876 877 878 879 880 |
# File 'lib/gitlab/database/migration_helpers.rb', line 876 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)
724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 |
# File 'lib/gitlab/database/migration_helpers.rb', line 724 def initialize_conversion_of_integer_to_bigint(table, columns, primary_key: :id) mappings = Array(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 ) 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).
530 531 532 |
# File 'lib/gitlab/database/migration_helpers.rb', line 530 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 |
#partition?(table_name) ⇒ Boolean
rubocop:enable Metrics/CyclomaticComplexity,Metrics/PerceivedComplexity
1180 1181 1182 1183 1184 1185 1186 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1180 def partition?(table_name) if view_exists?(:postgres_partitions) Gitlab::Database::PostgresPartition.partition_exists?(table_name) else Gitlab::Database::PostgresPartition.legacy_partition_exists?(table_name) end end |
#postgres_exists_by_name?(table, name) ⇒ Boolean
1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1033 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.
1199 1200 1201 1202 1203 1204 1205 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1199 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.
153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 |
# File 'lib/gitlab/database/migration_helpers.rb', line 153 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.
188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 |
# File 'lib/gitlab/database/migration_helpers.rb', line 188 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
973 974 975 976 977 978 979 980 981 982 983 984 985 986 |
# File 'lib/gitlab/database/migration_helpers.rb', line 973 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
988 989 990 991 |
# File 'lib/gitlab/database/migration_helpers.rb', line 988 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.
535 536 537 |
# File 'lib/gitlab/database/migration_helpers.rb', line 535 def remove_rename_triggers(table, trigger) Gitlab::Database::UnidirectionalCopyTrigger.on_table(table, connection: connection).drop(trigger) 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.
77 78 79 80 81 82 |
# File 'lib/gitlab/database/migration_helpers.rb', line 77 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
489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 |
# File 'lib/gitlab/database/migration_helpers.rb', line 489 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.
540 541 542 |
# File 'lib/gitlab/database/migration_helpers.rb', line 540 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`
962 963 964 965 966 967 968 969 970 971 |
# File 'lib/gitlab/database/migration_helpers.rb', line 962 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)
763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 |
# File 'lib/gitlab/database/migration_helpers.rb', line 763 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)
860 861 862 863 864 865 866 867 868 869 870 871 872 873 |
# File 'lib/gitlab/database/migration_helpers.rb', line 860 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.
747 748 749 750 751 752 753 754 755 |
# File 'lib/gitlab/database/migration_helpers.rb', line 747 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) } end |
#swap_primary_key(table_name, primary_key_name, index_to_use) ⇒ Object Also known as: unswap_primary_key
1077 1078 1079 1080 1081 1082 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1077 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_partitioned?(table_name) ⇒ Boolean
1188 1189 1190 1191 1192 |
# File 'lib/gitlab/database/migration_helpers.rb', line 1188 def table_partitioned?(table_name) Gitlab::Database::PostgresPartitionedTable .find_by_name_in_current_schema(table_name) .present? end |
#true_value ⇒ Object
357 358 359 |
# File 'lib/gitlab/database/migration_helpers.rb', line 357 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.
559 560 561 562 563 |
# File 'lib/gitlab/database/migration_helpers.rb', line 559 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
675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 |
# File 'lib/gitlab/database/migration_helpers.rb', line 675 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) ⇒ 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
589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 |
# File 'lib/gitlab/database/migration_helpers.rb', line 589 def undo_cleanup_concurrent_column_type_change(table, column, old_type, type_cast_function: nil, batch_column_name: :id, limit: 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.
513 514 515 516 517 518 519 520 521 |
# File 'lib/gitlab/database/migration_helpers.rb', line 513 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
410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 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 |
# File 'lib/gitlab/database/migration_helpers.rb', line 410 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
295 296 297 298 299 300 301 302 303 304 305 |
# File 'lib/gitlab/database/migration_helpers.rb', line 295 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 |