From af72fa9c9c0af25cb7d8d349abd9dea4897aeea8 Mon Sep 17 00:00:00 2001 From: Grzegorz Bizon Date: Mon, 5 Jun 2017 13:21:49 +0200 Subject: Migrate pipeline stages only when not migrated already --- db/post_migrate/20170526185842_migrate_pipeline_stages.rb | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) diff --git a/db/post_migrate/20170526185842_migrate_pipeline_stages.rb b/db/post_migrate/20170526185842_migrate_pipeline_stages.rb index 05e095f07cb..382791c4664 100644 --- a/db/post_migrate/20170526185842_migrate_pipeline_stages.rb +++ b/db/post_migrate/20170526185842_migrate_pipeline_stages.rb @@ -11,9 +11,9 @@ class MigratePipelineStages < ActiveRecord::Migration execute <<-SQL.strip_heredoc INSERT INTO ci_stages (project_id, pipeline_id, name) SELECT project_id, commit_id, stage FROM ci_builds - WHERE stage IS NOT NULL - GROUP BY project_id, commit_id, stage, stage_idx - ORDER BY stage_idx + WHERE stage IS NOT NULL AND stage_id IS NULL + GROUP BY project_id, commit_id, stage + ORDER BY MAX(stage_idx) SQL end -- cgit v1.2.1 From b72abd1d359da313b31097fcf042d2379e1c4ab2 Mon Sep 17 00:00:00 2001 From: Grzegorz Bizon Date: Mon, 5 Jun 2017 13:24:07 +0200 Subject: Migrate stage_id only it job does not have it already --- db/post_migrate/20170526185921_migrate_build_stage_reference.rb | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) diff --git a/db/post_migrate/20170526185921_migrate_build_stage_reference.rb b/db/post_migrate/20170526185921_migrate_build_stage_reference.rb index 8f453b8cd82..3c30dfd6dde 100644 --- a/db/post_migrate/20170526185921_migrate_build_stage_reference.rb +++ b/db/post_migrate/20170526185921_migrate_build_stage_reference.rb @@ -10,7 +10,9 @@ class MigrateBuildStageReference < ActiveRecord::Migration 'WHERE ci_stages.pipeline_id = ci_builds.commit_id ' \ 'AND ci_stages.name = ci_builds.stage)') - update_column_in_batches(:ci_builds, :stage_id, stage_id) + update_column_in_batches(:ci_builds, :stage_id, stage_id) do |table, query| + query.where(table[:stage_id].eq(nil)) + end end def down -- cgit v1.2.1 From 06898af38f46daaa1c75cb4adead971062684875 Mon Sep 17 00:00:00 2001 From: Grzegorz Bizon Date: Mon, 5 Jun 2017 13:35:35 +0200 Subject: Create indexes on pipeline stages before migration Creates an index in ci_stages before migrating pipeline stages from ci_builds, to improve migration performance. --- .../20170526185748_create_index_in_pipeline_stages.rb | 15 +++++++++++++++ .../20170526190948_create_index_in_pipeline_stages.rb | 15 --------------- 2 files changed, 15 insertions(+), 15 deletions(-) create mode 100644 db/post_migrate/20170526185748_create_index_in_pipeline_stages.rb delete mode 100644 db/post_migrate/20170526190948_create_index_in_pipeline_stages.rb diff --git a/db/post_migrate/20170526185748_create_index_in_pipeline_stages.rb b/db/post_migrate/20170526185748_create_index_in_pipeline_stages.rb new file mode 100644 index 00000000000..d049f87578a --- /dev/null +++ b/db/post_migrate/20170526185748_create_index_in_pipeline_stages.rb @@ -0,0 +1,15 @@ +class CreateIndexInPipelineStages < ActiveRecord::Migration + include Gitlab::Database::MigrationHelpers + + DOWNTIME = false + + disable_ddl_transaction! + + def up + add_concurrent_index(:ci_stages, [:pipeline_id, :name]) + end + + def down + remove_index(:ci_stages, [:pipeline_id, :name]) + end +end diff --git a/db/post_migrate/20170526190948_create_index_in_pipeline_stages.rb b/db/post_migrate/20170526190948_create_index_in_pipeline_stages.rb deleted file mode 100644 index d049f87578a..00000000000 --- a/db/post_migrate/20170526190948_create_index_in_pipeline_stages.rb +++ /dev/null @@ -1,15 +0,0 @@ -class CreateIndexInPipelineStages < ActiveRecord::Migration - include Gitlab::Database::MigrationHelpers - - DOWNTIME = false - - disable_ddl_transaction! - - def up - add_concurrent_index(:ci_stages, [:pipeline_id, :name]) - end - - def down - remove_index(:ci_stages, [:pipeline_id, :name]) - end -end -- cgit v1.2.1 From 3326291f7e6c6b4452ca6aa4fc65faf1bd821220 Mon Sep 17 00:00:00 2001 From: Grzegorz Bizon Date: Mon, 5 Jun 2017 13:46:42 +0200 Subject: Remove obsolete stages/build before adding foreign keys --- ...0526190708_create_foreign_keys_for_pipeline_stages.rb | 16 ++++++++++++++++ 1 file changed, 16 insertions(+) diff --git a/db/post_migrate/20170526190708_create_foreign_keys_for_pipeline_stages.rb b/db/post_migrate/20170526190708_create_foreign_keys_for_pipeline_stages.rb index 40060d12d74..8a01de1fce6 100644 --- a/db/post_migrate/20170526190708_create_foreign_keys_for_pipeline_stages.rb +++ b/db/post_migrate/20170526190708_create_foreign_keys_for_pipeline_stages.rb @@ -6,6 +6,22 @@ class CreateForeignKeysForPipelineStages < ActiveRecord::Migration disable_ddl_transaction! def up + execute <<~SQL + DELETE FROM ci_stages + WHERE NOT EXISTS ( + SELECT true FROM projects + WHERE projects.id = ci_stages.project_id + ) + SQL + + execute <<~SQL + DELETE FROM ci_builds + WHERE NOT EXISTS ( + SELECT true FROM ci_stages + WHERE ci_stages.id = ci_builds.stage_id + ) + SQL + add_concurrent_foreign_key :ci_stages, :projects, column: :project_id, on_delete: :cascade add_concurrent_foreign_key :ci_builds, :ci_stages, column: :stage_id, on_delete: :cascade end -- cgit v1.2.1 From 7a2ad70f0b143e3fd195e6e5c475a7b7350c527c Mon Sep 17 00:00:00 2001 From: Grzegorz Bizon Date: Mon, 5 Jun 2017 13:48:41 +0200 Subject: Disable timeouts in foreign keys for stages migration --- .../20170526190708_create_foreign_keys_for_pipeline_stages.rb | 2 ++ 1 file changed, 2 insertions(+) diff --git a/db/post_migrate/20170526190708_create_foreign_keys_for_pipeline_stages.rb b/db/post_migrate/20170526190708_create_foreign_keys_for_pipeline_stages.rb index 8a01de1fce6..5ae3c1ae098 100644 --- a/db/post_migrate/20170526190708_create_foreign_keys_for_pipeline_stages.rb +++ b/db/post_migrate/20170526190708_create_foreign_keys_for_pipeline_stages.rb @@ -6,6 +6,8 @@ class CreateForeignKeysForPipelineStages < ActiveRecord::Migration disable_ddl_transaction! def up + disable_statement_timeout + execute <<~SQL DELETE FROM ci_stages WHERE NOT EXISTS ( -- cgit v1.2.1 From faeb2f3a09d18a0041a6c425a60f26ed9a41498a Mon Sep 17 00:00:00 2001 From: Grzegorz Bizon Date: Mon, 5 Jun 2017 14:54:39 +0200 Subject: Remove stage index concurrently on migration rollback --- db/post_migrate/20170526185748_create_index_in_pipeline_stages.rb | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/db/post_migrate/20170526185748_create_index_in_pipeline_stages.rb b/db/post_migrate/20170526185748_create_index_in_pipeline_stages.rb index d049f87578a..ec9ff33b6b7 100644 --- a/db/post_migrate/20170526185748_create_index_in_pipeline_stages.rb +++ b/db/post_migrate/20170526185748_create_index_in_pipeline_stages.rb @@ -10,6 +10,6 @@ class CreateIndexInPipelineStages < ActiveRecord::Migration end def down - remove_index(:ci_stages, [:pipeline_id, :name]) + remove_concurrent_index(:ci_stages, [:pipeline_id, :name]) end end -- cgit v1.2.1 From cafb1bfea4dbc7b6aad47580e76d68b8075d177f Mon Sep 17 00:00:00 2001 From: Grzegorz Bizon Date: Mon, 5 Jun 2017 14:56:53 +0200 Subject: Use the latest migration version as a schema version --- db/schema.rb | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/db/schema.rb b/db/schema.rb index 47484e6b8fe..23c3ecdd517 100644 --- a/db/schema.rb +++ b/db/schema.rb @@ -11,7 +11,7 @@ # # It's strongly recommended that you check this file into your version control system. -ActiveRecord::Schema.define(version: 20170526190948) do +ActiveRecord::Schema.define(version: 20170526190708) do # These are extensions that must be enabled in order to support this database enable_extension "plpgsql" -- cgit v1.2.1