diff options
author | Robert Speicher <robert@gitlab.com> | 2016-05-19 19:49:14 +0300 |
---|---|---|
committer | Robert Speicher <robert@gitlab.com> | 2016-05-19 19:49:14 +0300 |
commit | 883e2d2187a9abbfcd7b90adc3bd84edcd45db16 (patch) | |
tree | dc6844aa2d36025b81467606349a13fff2bd5eba /lib | |
parent | 9cd21b325dcdff96e59f8ab496b99d74274fca4d (diff) | |
parent | 7e7764139d171ceeee5200b2677bdab0f8ab7c2d (diff) |
Merge branch 'migration-helpers' into 'master'
Added helper methods for database migrations
These helpers can ultimately be used to write migrations that don't
require downtime.
See #15464 for more information.
See merge request !3860
Diffstat (limited to 'lib')
-rw-r--r-- | lib/gitlab/database/migration_helpers.rb | 134 | ||||
-rw-r--r-- | lib/templates/active_record/migration/create_table_migration.rb | 35 | ||||
-rw-r--r-- | lib/templates/active_record/migration/migration.rb | 55 |
3 files changed, 224 insertions, 0 deletions
diff --git a/lib/gitlab/database/migration_helpers.rb b/lib/gitlab/database/migration_helpers.rb new file mode 100644 index 00000000000..31a87b73fbe --- /dev/null +++ b/lib/gitlab/database/migration_helpers.rb @@ -0,0 +1,134 @@ +module Gitlab + module Database + module MigrationHelpers + # Creates a new index, concurrently when supported + # + # On PostgreSQL this method creates an index concurrently, on MySQL this + # creates a regular index. + # + # Example: + # + # add_concurrent_index :users, :some_column + # + # See Rails' `add_index` for more info on the available arguments. + def add_concurrent_index(*args) + 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 Database.postgresql? + args << { algorithm: :concurrently } + end + + add_index(*args) + end + + # Updates the value of a column in batches. + # + # This method updates the table in batches of 5% of the total row count. + # Any data inserted while running this method (or after it has finished + # running) is _not_ updated automatically. + # + # This method _only_ updates rows where the column's value is set to NULL. + # + # table - The name of the table. + # column - The name of the column to update. + # value - The value for the column. + def update_column_in_batches(table, column, value) + quoted_table = quote_table_name(table) + quoted_column = quote_column_name(column) + quoted_value = quote(value) + processed = 0 + + total = exec_query("SELECT COUNT(*) AS count FROM #{quoted_table}"). + to_hash. + first['count']. + to_i + + # Update in batches of 5% with an upper limit of 5000 rows. + batch_size = ((total / 100.0) * 5.0).ceil + + while processed < total + start_row = exec_query(%Q{ + SELECT id + FROM #{quoted_table} + ORDER BY id ASC + LIMIT 1 OFFSET #{processed} + }).to_hash.first + + stop_row = exec_query(%Q{ + SELECT id + FROM #{quoted_table} + ORDER BY id ASC + LIMIT 1 OFFSET #{processed + batch_size} + }).to_hash.first + + query = %Q{ + UPDATE #{quoted_table} + SET #{quoted_column} = #{quoted_value} + WHERE id >= #{start_row['id']} + } + + if stop_row + query += " AND id < #{stop_row['id']}" + end + + execute(query) + + processed += batch_size + end + end + + # Adds a column with a default value without locking an entire table. + # + # This method runs the following steps: + # + # 1. Add the column with a default value of NULL. + # 2. Update all existing rows in batches. + # 3. Change the default value of the column to the specified value. + # 4. Update any remaining rows. + # + # These steps ensure a column can be added to a large and commonly used + # table without locking the entire table for the duration of the table + # modification. + # + # table - The name of the table to update. + # column - The name of the column to add. + # type - The column type (e.g. `:integer`). + # default - The default value for the column. + # allow_null - When set to `true` the column will allow NULL values, the + # default is to not allow NULL values. + def add_column_with_default(table, column, type, default:, allow_null: false) + if transaction_open? + raise 'add_column_with_default can not be run inside a transaction, ' \ + 'you can disable transactions by calling disable_ddl_transaction! ' \ + 'in the body of your migration class' + end + + transaction do + add_column(table, column, type, default: nil) + + # Changing the default before the update ensures any newly inserted + # rows already use the proper default value. + change_column_default(table, column, default) + end + + begin + transaction do + update_column_in_batches(table, column, default) + end + # We want to rescue _all_ exceptions here, even those that don't inherit + # from StandardError. + rescue Exception => error # rubocop: disable all + remove_column(table, column) + + raise error + end + + change_column_null(table, column, false) unless allow_null + end + end + end +end diff --git a/lib/templates/active_record/migration/create_table_migration.rb b/lib/templates/active_record/migration/create_table_migration.rb new file mode 100644 index 00000000000..27acc75dcc4 --- /dev/null +++ b/lib/templates/active_record/migration/create_table_migration.rb @@ -0,0 +1,35 @@ +# See http://doc.gitlab.com/ce/development/migration_style_guide.html +# for more information on how to write migrations for GitLab. + +class <%= migration_class_name %> < ActiveRecord::Migration + include Gitlab::Database::MigrationHelpers + + # When using the methods "add_concurrent_index" or "add_column_with_default" + # you must disable the use of transactions as these methods can not run in an + # existing transaction. When using "add_concurrent_index" make sure that this + # method is the _only_ method called in the migration, any other changes + # should go in a separate migration. This ensures that upon failure _only_ the + # index creation fails and can be retried or reverted easily. + # + # To disable transactions uncomment the following line and remove these + # comments: + # disable_ddl_transaction! + + def change + create_table :<%= table_name %> do |t| +<% attributes.each do |attribute| -%> +<% if attribute.password_digest? -%> + t.string :password_digest<%= attribute.inject_options %> +<% else -%> + t.<%= attribute.type %> :<%= attribute.name %><%= attribute.inject_options %> +<% end -%> +<% end -%> +<% if options[:timestamps] %> + t.timestamps null: false +<% end -%> + end +<% attributes_with_index.each do |attribute| -%> + add_index :<%= table_name %>, :<%= attribute.index_name %><%= attribute.inject_index_options %> +<% end -%> + end +end diff --git a/lib/templates/active_record/migration/migration.rb b/lib/templates/active_record/migration/migration.rb new file mode 100644 index 00000000000..06bdea11367 --- /dev/null +++ b/lib/templates/active_record/migration/migration.rb @@ -0,0 +1,55 @@ +# See http://doc.gitlab.com/ce/development/migration_style_guide.html +# for more information on how to write migrations for GitLab. + +class <%= migration_class_name %> < ActiveRecord::Migration + include Gitlab::Database::MigrationHelpers + + # When using the methods "add_concurrent_index" or "add_column_with_default" + # you must disable the use of transactions as these methods can not run in an + # existing transaction. When using "add_concurrent_index" make sure that this + # method is the _only_ method called in the migration, any other changes + # should go in a separate migration. This ensures that upon failure _only_ the + # index creation fails and can be retried or reverted easily. + # + # To disable transactions uncomment the following line and remove these + # comments: + # disable_ddl_transaction! + +<%- if migration_action == 'add' -%> + def change +<% attributes.each do |attribute| -%> + <%- if attribute.reference? -%> + add_reference :<%= table_name %>, :<%= attribute.name %><%= attribute.inject_options %> + <%- else -%> + add_column :<%= table_name %>, :<%= attribute.name %>, :<%= attribute.type %><%= attribute.inject_options %> + <%- if attribute.has_index? -%> + add_index :<%= table_name %>, :<%= attribute.index_name %><%= attribute.inject_index_options %> + <%- end -%> + <%- end -%> +<%- end -%> + end +<%- elsif migration_action == 'join' -%> + def change + create_join_table :<%= join_tables.first %>, :<%= join_tables.second %> do |t| + <%- attributes.each do |attribute| -%> + <%= '# ' unless attribute.has_index? -%>t.index <%= attribute.index_name %><%= attribute.inject_index_options %> + <%- end -%> + end + end +<%- else -%> + def change +<% attributes.each do |attribute| -%> +<%- if migration_action -%> + <%- if attribute.reference? -%> + remove_reference :<%= table_name %>, :<%= attribute.name %><%= attribute.inject_options %> + <%- else -%> + <%- if attribute.has_index? -%> + remove_index :<%= table_name %>, :<%= attribute.index_name %><%= attribute.inject_index_options %> + <%- end -%> + remove_column :<%= table_name %>, :<%= attribute.name %>, :<%= attribute.type %><%= attribute.inject_options %> + <%- end -%> +<%- end -%> +<%- end -%> + end +<%- end -%> +end |