From eb21f93267f3d911b40cace81a4f63f857a1243a Mon Sep 17 00:00:00 2001 From: Nick Thomas Date: Thu, 25 May 2017 12:10:34 +0100 Subject: Only use DROP INDEX CONCURRENTLY on postgreql 9.2+ --- .gitlab-ci.yml | 2 +- .../20170317203554_index_routes_path_for_like.rb | 5 ++-- ...18_remove_index_for_users_current_sign_in_at.rb | 8 +---- ...03185032_index_redirect_routes_path_for_like.rb | 5 ++-- lib/gitlab/database/migration_helpers.rb | 35 +++++++++++++++++++++- spec/lib/gitlab/database/migration_helpers_spec.rb | 11 +++++-- 6 files changed, 49 insertions(+), 17 deletions(-) diff --git a/.gitlab-ci.yml b/.gitlab-ci.yml index 638553d7bf7..f13a00bd984 100644 --- a/.gitlab-ci.yml +++ b/.gitlab-ci.yml @@ -52,7 +52,7 @@ stages: .use-pg: &use-pg services: - - postgres:9.2 + - postgres:9.1 - redis:alpine .use-mysql: &use-mysql diff --git a/db/migrate/20170317203554_index_routes_path_for_like.rb b/db/migrate/20170317203554_index_routes_path_for_like.rb index 7ac09b2abe5..8d3609135d0 100644 --- a/db/migrate/20170317203554_index_routes_path_for_like.rb +++ b/db/migrate/20170317203554_index_routes_path_for_like.rb @@ -21,9 +21,8 @@ class IndexRoutesPathForLike < ActiveRecord::Migration def down return unless Gitlab::Database.postgresql? + return unless index_exists?(:routes, :path, name: INDEX_NAME) - if index_exists?(:routes, :path, name: INDEX_NAME) - execute("DROP INDEX CONCURRENTLY #{INDEX_NAME};") - end + remove_concurrent_index_by_name(:routes, INDEX_NAME) end end diff --git a/db/migrate/20170402231018_remove_index_for_users_current_sign_in_at.rb b/db/migrate/20170402231018_remove_index_for_users_current_sign_in_at.rb index 9d4380ef960..84635fa39b9 100644 --- a/db/migrate/20170402231018_remove_index_for_users_current_sign_in_at.rb +++ b/db/migrate/20170402231018_remove_index_for_users_current_sign_in_at.rb @@ -11,13 +11,7 @@ class RemoveIndexForUsersCurrentSignInAt < ActiveRecord::Migration disable_ddl_transaction! def up - if index_exists? :users, :current_sign_in_at - if Gitlab::Database.postgresql? - execute 'DROP INDEX CONCURRENTLY index_users_on_current_sign_in_at;' - else - remove_concurrent_index :users, :current_sign_in_at - end - end + remove_concurrent_index :users, :current_sign_in_at end def down diff --git a/db/migrate/20170503185032_index_redirect_routes_path_for_like.rb b/db/migrate/20170503185032_index_redirect_routes_path_for_like.rb index 5b8b6c828be..8eb20faa03a 100644 --- a/db/migrate/20170503185032_index_redirect_routes_path_for_like.rb +++ b/db/migrate/20170503185032_index_redirect_routes_path_for_like.rb @@ -21,9 +21,8 @@ class IndexRedirectRoutesPathForLike < ActiveRecord::Migration def down return unless Gitlab::Database.postgresql? + return unless index_exists?(:redirect_routes, :path, name: INDEX_NAME) - if index_exists?(:redirect_routes, :path, name: INDEX_NAME) - execute("DROP INDEX CONCURRENTLY #{INDEX_NAME};") - end + remove_concurrent_index_by_name(:redirect_routes, INDEX_NAME) end end diff --git a/lib/gitlab/database/migration_helpers.rb b/lib/gitlab/database/migration_helpers.rb index e76c9abbe04..a412bb6dbd2 100644 --- a/lib/gitlab/database/migration_helpers.rb +++ b/lib/gitlab/database/migration_helpers.rb @@ -42,7 +42,7 @@ module Gitlab 'in the body of your migration class' end - if Database.postgresql? + if supports_drop_index_concurrently? options = options.merge({ algorithm: :concurrently }) disable_statement_timeout end @@ -50,6 +50,39 @@ module Gitlab remove_index(table_name, options.merge({ column: column_name })) end + # Removes an existing index, concurrently when supported + # + # On PostgreSQL this method removes an index concurrently. + # + # Example: + # + # remove_concurrent_index :users, "index_X_by_Y" + # + # See Rails' `remove_index` for more info on the available arguments. + def remove_concurrent_index_by_name(table_name, index_name, options = {}) + 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 supports_drop_index_concurrently? + options = options.merge({ algorithm: :concurrently }) + disable_statement_timeout + end + + remove_index(table_name, options.merge({ name: index_name })) + end + + # Only available on Postgresql >= 9.2 + def supports_drop_index_concurrently? + return false unless Database.postgresql? + + version = select_one("SELECT current_setting('server_version_num') AS v")['v'].to_i + + version >= 90200 + end + # Adds a foreign key with only minimal locking on the tables involved. # # This method only requires minimal locking when using PostgreSQL. When diff --git a/spec/lib/gitlab/database/migration_helpers_spec.rb b/spec/lib/gitlab/database/migration_helpers_spec.rb index bd5ac6142be..3fdafd867da 100644 --- a/spec/lib/gitlab/database/migration_helpers_spec.rb +++ b/spec/lib/gitlab/database/migration_helpers_spec.rb @@ -66,16 +66,23 @@ describe Gitlab::Database::MigrationHelpers, lib: true do context 'using PostgreSQL' do before do - allow(Gitlab::Database).to receive(:postgresql?).and_return(true) + allow(model).to receive(:supports_drop_index_concurrently?).and_return(true) allow(model).to receive(:disable_statement_timeout) end - it 'removes the index concurrently' do + it 'removes the index concurrently by column name' do expect(model).to receive(:remove_index). with(:users, { algorithm: :concurrently, column: :foo }) model.remove_concurrent_index(:users, :foo) end + + it 'removes the index concurrently by index name' do + expect(model).to receive(:remove_index). + with(:users, { algorithm: :concurrently, name: "index_x_by_y" }) + + model.remove_concurrent_index_by_name(:users, "index_x_by_y") + end end context 'using MySQL' do -- cgit v1.2.1