From 47d03aaf5c89162e9b5a689c46c1c179803e6198 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Alejandro=20Rodr=C3=ADguez?= <alejorro70@gmail.com>
Date: Tue, 20 Sep 2016 22:02:27 -0300
Subject: [PATCH 1/5] Use foreign keys with cascade delete to optimize project
 removal

---
 app/models/board.rb                           |   2 +-
 app/models/ci/pipeline.rb                     |   2 +-
 app/models/ci/trigger.rb                      |   2 +-
 app/models/label.rb                           |   4 +-
 app/models/merge_request.rb                   |   2 +-
 app/models/note.rb                            |   2 +-
 app/models/project.rb                         | 103 +++++-----
 app/models/protected_branch.rb                |   4 +-
 ...514_add_foreign_keys_for_cascade_delete.rb | 186 ++++++++++++++++++
 db/schema.rb                                  |  39 +++-
 spec/models/board_spec.rb                     |   2 +-
 spec/models/concerns/issuable_spec.rb         |   2 +-
 spec/models/forked_project_link_spec.rb       |   5 -
 spec/models/label_spec.rb                     |   4 +-
 spec/models/merge_request_spec.rb             |   2 +-
 spec/models/note_spec.rb                      |   2 +-
 spec/models/project_spec.rb                   |  90 ++++-----
 17 files changed, 332 insertions(+), 121 deletions(-)
 create mode 100644 db/migrate/20160907201514_add_foreign_keys_for_cascade_delete.rb

diff --git a/app/models/board.rb b/app/models/board.rb
index c56422914a94..434b8ff00901 100644
--- a/app/models/board.rb
+++ b/app/models/board.rb
@@ -1,7 +1,7 @@
 class Board < ActiveRecord::Base
   belongs_to :project
 
-  has_many :lists, -> { order(:list_type, :position) }, dependent: :delete_all
+  has_many :lists, -> { order(:list_type, :position) }
 
   validates :project, presence: true
 
diff --git a/app/models/ci/pipeline.rb b/app/models/ci/pipeline.rb
index fabbf97d4db3..72575b0369f6 100644
--- a/app/models/ci/pipeline.rb
+++ b/app/models/ci/pipeline.rb
@@ -12,7 +12,7 @@ class Pipeline < ActiveRecord::Base
 
     has_many :statuses, class_name: 'CommitStatus', foreign_key: :commit_id
     has_many :builds, foreign_key: :commit_id
-    has_many :trigger_requests, dependent: :destroy, foreign_key: :commit_id
+    has_many :trigger_requests, foreign_key: :commit_id
 
     validates_presence_of :sha, unless: :importing?
     validates_presence_of :ref, unless: :importing?
diff --git a/app/models/ci/trigger.rb b/app/models/ci/trigger.rb
index 62889fe80d8c..5676da97fddb 100644
--- a/app/models/ci/trigger.rb
+++ b/app/models/ci/trigger.rb
@@ -5,7 +5,7 @@ class Trigger < ActiveRecord::Base
     acts_as_paranoid
 
     belongs_to :project, foreign_key: :gl_project_id
-    has_many :trigger_requests, dependent: :destroy
+    has_many :trigger_requests
 
     validates_presence_of :token
     validates_uniqueness_of :token
diff --git a/app/models/label.rb b/app/models/label.rb
index d9287f2dc290..1fed49c3b5ca 100644
--- a/app/models/label.rb
+++ b/app/models/label.rb
@@ -15,9 +15,9 @@ class Label < ActiveRecord::Base
 
   default_value_for :color, DEFAULT_COLOR
 
-  has_many :lists, dependent: :destroy
+  has_many :lists
   has_many :priorities, class_name: 'LabelPriority'
-  has_many :label_links, dependent: :destroy
+  has_many :label_links
   has_many :issues, through: :label_links, source: :target, source_type: 'Issue'
   has_many :merge_requests, through: :label_links, source: :target, source_type: 'MergeRequest'
 
diff --git a/app/models/merge_request.rb b/app/models/merge_request.rb
index bfb016df46d3..fc154fff5765 100644
--- a/app/models/merge_request.rb
+++ b/app/models/merge_request.rb
@@ -9,7 +9,7 @@ class MergeRequest < ActiveRecord::Base
   belongs_to :source_project, class_name: "Project"
   belongs_to :merge_user, class_name: "User"
 
-  has_many :merge_request_diffs, dependent: :destroy
+  has_many :merge_request_diffs
   has_one :merge_request_diff,
     -> { order('merge_request_diffs.id DESC') }
 
diff --git a/app/models/note.rb b/app/models/note.rb
index 5b50ca285c3a..fe430c4917b4 100644
--- a/app/models/note.rb
+++ b/app/models/note.rb
@@ -35,7 +35,7 @@ class Note < ActiveRecord::Base
   # Only used by DiffNote, but defined here so that it can be used in `Note.includes`
   belongs_to :resolved_by, class_name: "User"
 
-  has_many :todos, dependent: :destroy
+  has_many :todos
   has_many :events, as: :target, dependent: :destroy
 
   delegate :gfm_reference, :local_reference, to: :noteable
diff --git a/app/models/project.rb b/app/models/project.rb
index f01cb613b857..29c9ab340431 100644
--- a/app/models/project.rb
+++ b/app/models/project.rb
@@ -77,54 +77,55 @@ def update_forks_visibility_level
   belongs_to :namespace
 
   has_one :last_event, -> {order 'events.created_at DESC'}, class_name: 'Event'
-  has_many :boards, before_add: :validate_board_limit, dependent: :destroy
+
+  has_many :boards, before_add: :validate_board_limit
   has_many :chat_services
 
   # Project services
-  has_one :campfire_service, dependent: :destroy
-  has_one :drone_ci_service, dependent: :destroy
-  has_one :emails_on_push_service, dependent: :destroy
-  has_one :builds_email_service, dependent: :destroy
-  has_one :pipelines_email_service, dependent: :destroy
-  has_one :irker_service, dependent: :destroy
-  has_one :pivotaltracker_service, dependent: :destroy
-  has_one :hipchat_service, dependent: :destroy
-  has_one :flowdock_service, dependent: :destroy
-  has_one :assembla_service, dependent: :destroy
-  has_one :asana_service, dependent: :destroy
-  has_one :gemnasium_service, dependent: :destroy
-  has_one :mattermost_slash_commands_service, dependent: :destroy
-  has_one :slack_service, dependent: :destroy
-  has_one :buildkite_service, dependent: :destroy
-  has_one :bamboo_service, dependent: :destroy
-  has_one :teamcity_service, dependent: :destroy
-  has_one :pushover_service, dependent: :destroy
-  has_one :jira_service, dependent: :destroy
-  has_one :redmine_service, dependent: :destroy
-  has_one :custom_issue_tracker_service, dependent: :destroy
-  has_one :bugzilla_service, dependent: :destroy
-  has_one :gitlab_issue_tracker_service, dependent: :destroy, inverse_of: :project
-  has_one :external_wiki_service, dependent: :destroy
-
-  has_one  :forked_project_link,  dependent: :destroy, foreign_key: "forked_to_project_id"
+  has_one :campfire_service
+  has_one :drone_ci_service
+  has_one :emails_on_push_service
+  has_one :builds_email_service
+  has_one :pipelines_email_service
+  has_one :irker_service
+  has_one :pivotaltracker_service
+  has_one :hipchat_service
+  has_one :flowdock_service
+  has_one :assembla_service
+  has_one :asana_service
+  has_one :gemnasium_service
+  has_one :mattermost_slash_commands_service
+  has_one :slack_service
+  has_one :buildkite_service
+  has_one :bamboo_service
+  has_one :teamcity_service
+  has_one :pushover_service
+  has_one :jira_service
+  has_one :redmine_service
+  has_one :custom_issue_tracker_service
+  has_one :bugzilla_service
+  has_one :gitlab_issue_tracker_service, inverse_of: :project
+  has_one :external_wiki_service
+
+  has_one  :forked_project_link, foreign_key: "forked_to_project_id"
   has_one  :forked_from_project,  through:   :forked_project_link
 
   has_many :forked_project_links, foreign_key: "forked_from_project_id"
   has_many :forks,                through:     :forked_project_links, source: :forked_to_project
 
   # Merge Requests for target project should be removed with it
-  has_many :merge_requests,     dependent: :destroy, foreign_key: 'target_project_id'
+  has_many :merge_requests, foreign_key: 'target_project_id'
   # Merge requests from source project should be kept when source project was removed
   has_many :fork_merge_requests, foreign_key: 'source_project_id', class_name: MergeRequest
-  has_many :issues,             dependent: :destroy
-  has_many :labels,             dependent: :destroy, class_name: 'ProjectLabel'
-  has_many :services,           dependent: :destroy
-  has_many :events,             dependent: :destroy
-  has_many :milestones,         dependent: :destroy
-  has_many :notes,              dependent: :destroy
-  has_many :snippets,           dependent: :destroy, class_name: 'ProjectSnippet'
-  has_many :hooks,              dependent: :destroy, class_name: 'ProjectHook'
-  has_many :protected_branches, dependent: :destroy
+  has_many :issues
+  has_many :labels, class_name: 'ProjectLabel'
+  has_many :services
+  has_many :events
+  has_many :milestones
+  has_many :notes
+  has_many :snippets, class_name: 'ProjectSnippet'
+  has_many :hooks, class_name: 'ProjectHook'
+  has_many :protected_branches
 
   has_many :project_authorizations, dependent: :destroy
   has_many :authorized_users, through: :project_authorizations, source: :user, class_name: 'User'
@@ -136,28 +137,28 @@ def update_forks_visibility_level
 
   has_many :deploy_keys_projects, dependent: :destroy
   has_many :deploy_keys, through: :deploy_keys_projects
-  has_many :users_star_projects, dependent: :destroy
+  has_many :users_star_projects
   has_many :starrers, through: :users_star_projects, source: :user
-  has_many :releases, dependent: :destroy
-  has_many :lfs_objects_projects, dependent: :destroy
+  has_many :releases
+  has_many :lfs_objects_projects
   has_many :lfs_objects, through: :lfs_objects_projects
-  has_many :project_group_links, dependent: :destroy
+  has_many :project_group_links
   has_many :invited_groups, through: :project_group_links, source: :group
-  has_many :todos, dependent: :destroy
-  has_many :notification_settings, dependent: :destroy, as: :source
+  has_many :todos
+  has_many :notification_settings, as: :source
 
-  has_one :import_data, dependent: :destroy, class_name: "ProjectImportData"
-  has_one :project_feature, dependent: :destroy
+  has_one :import_data, class_name: "ProjectImportData"
+  has_one :project_feature
 
-  has_many :commit_statuses, dependent: :destroy, foreign_key: :gl_project_id
-  has_many :pipelines, dependent: :destroy, class_name: 'Ci::Pipeline', foreign_key: :gl_project_id
+  has_many :commit_statuses, foreign_key: :gl_project_id
+  has_many :pipelines, class_name: 'Ci::Pipeline', foreign_key: :gl_project_id
   has_many :builds, class_name: 'Ci::Build', foreign_key: :gl_project_id # the builds are created from the commit_statuses
-  has_many :runner_projects, dependent: :destroy, class_name: 'Ci::RunnerProject', foreign_key: :gl_project_id
+  has_many :runner_projects, class_name: 'Ci::RunnerProject', foreign_key: :gl_project_id
   has_many :runners, through: :runner_projects, source: :runner, class_name: 'Ci::Runner'
-  has_many :variables, dependent: :destroy, class_name: 'Ci::Variable', foreign_key: :gl_project_id
+  has_many :variables, class_name: 'Ci::Variable', foreign_key: :gl_project_id
   has_many :triggers, dependent: :destroy, class_name: 'Ci::Trigger', foreign_key: :gl_project_id
-  has_many :environments, dependent: :destroy
-  has_many :deployments, dependent: :destroy
+  has_many :environments
+  has_many :deployments
 
   accepts_nested_attributes_for :variables, allow_destroy: true
   accepts_nested_attributes_for :project_feature
diff --git a/app/models/protected_branch.rb b/app/models/protected_branch.rb
index 6240912a6e1a..defb365b7bdd 100644
--- a/app/models/protected_branch.rb
+++ b/app/models/protected_branch.rb
@@ -5,8 +5,8 @@ class ProtectedBranch < ActiveRecord::Base
   validates :name, presence: true
   validates :project, presence: true
 
-  has_many :merge_access_levels, dependent: :destroy
-  has_many :push_access_levels, dependent: :destroy
+  has_many :merge_access_levels
+  has_many :push_access_levels
 
   validates_length_of :merge_access_levels, is: 1, message: "are restricted to a single instance per protected branch."
   validates_length_of :push_access_levels, is: 1, message: "are restricted to a single instance per protected branch."
diff --git a/db/migrate/20160907201514_add_foreign_keys_for_cascade_delete.rb b/db/migrate/20160907201514_add_foreign_keys_for_cascade_delete.rb
new file mode 100644
index 000000000000..b2e4f5c34993
--- /dev/null
+++ b/db/migrate/20160907201514_add_foreign_keys_for_cascade_delete.rb
@@ -0,0 +1,186 @@
+# See http://doc.gitlab.com/ce/development/migration_style_guide.html
+# for more information on how to write migrations for GitLab.
+
+class AddForeignKeysForCascadeDelete < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  DOWNTIME = true
+  DOWNTIME_REASON = <<-HEREDOC
+    According to https://gocardless.com/blog/zero-downtime-postgres-migrations-the-hard-parts
+    adding a foreign key needs to acquire an AccessExclusive lock. Since we're
+    adding foreign keys in large, heavily used tables, this migration will require
+    downtime.
+  HEREDOC
+
+  def up
+    disable_statement_timeout
+
+    # Remove indexes to speed up orphan removal. We'll rebuild them after.
+    remove_index :issues, column: [:project_id, :iid] if index_exists?(:issues, [:project_id, :iid])
+    remove_index :issues, :assignee_id if index_exists?(:issues, :assignee_id)
+    remove_index :issues, :author_id if index_exists?(:issues, :author_id)
+    remove_index :issues, :confidential if index_exists?(:issues, :confidential)
+    remove_index :issues, :created_at if index_exists?(:issues, :created_at)
+    remove_index :issues, :deleted_at if index_exists?(:issues, :deleted_at)
+    remove_index :issues, :due_date if index_exists?(:issues, :due_date)
+    remove_index :issues, :milestone_id if index_exists?(:issues, :milestone_id)
+    remove_index :issues, :state if index_exists?(:issues, :state)
+    remove_index :events, :action if index_exists?(:events, :action)
+    remove_index :events, :author_id if index_exists?(:events, :author_id)
+    remove_index :events, :created_at if index_exists?(:events, :created_at)
+    remove_index :events, :project_id if index_exists?(:events, :project_id)
+    remove_index :events, :target_id if index_exists?(:events, :target_id)
+    remove_index :events, :target_type if index_exists?(:events, :target_type)
+
+    threads = []
+    threads << delete_project_orphans(:events, :project_id)
+    threads << delete_project_orphans(:issues, :project_id)
+    threads << delete_project_orphans(:merge_requests, :target_project_id)
+    threads << delete_project_orphans(:forked_project_links, :forked_to_project_id)
+    threads << delete_project_orphans(:services, :project_id)
+    threads << delete_project_orphans(:notes, :project_id)
+    threads.map(&:join)
+
+    # Rebuild indexes
+    add_index :issues, [:project_id, :iid], unique: true
+    add_index :issues, :assignee_id
+    add_index :issues, :author_id
+    add_index :issues, :confidential
+    add_index :issues, :created_at
+    add_index :issues, :deleted_at
+    add_index :issues, :due_date
+    add_index :issues, :milestone_id
+    add_index :issues, :state
+    add_index :events, :action
+    add_index :events, :author_id
+    add_index :events, :created_at
+    add_index :events, :project_id
+    add_index :events, :target_id
+    add_index :events, :target_type
+
+    # These already exist but don't specify on_delete: cascade, so we'll re-add them.
+    remove_foreign_key :boards, :projects
+    remove_foreign_key :lists, :boards
+    remove_foreign_key :lists, :labels
+    remove_foreign_key :protected_branch_merge_access_levels, :protected_branches
+    remove_foreign_key :protected_branch_push_access_levels, :protected_branches
+
+    # Merge Requests for target project and should be removed with it.
+    # Merge Requests from source project should be kept when source project was removed.
+    add_foreign_key :merge_requests, :projects, column: :target_project_id, on_delete: :cascade
+
+    add_foreign_key :forked_project_links, :projects, column: :forked_to_project_id, on_delete: :cascade
+    add_foreign_key :boards, :projects, on_delete: :cascade
+    add_foreign_key :lists, :boards, on_delete: :cascade
+    add_foreign_key :services, :projects, on_delete: :cascade
+    add_foreign_key :notes, :projects, on_delete: :cascade
+    add_foreign_key :events, :projects, on_delete: :cascade
+    add_foreign_key :todos, :notes, on_delete: :cascade
+    add_foreign_key :merge_request_diffs, :merge_requests, on_delete: :cascade
+    add_foreign_key :issues, :projects, on_delete: :cascade
+    add_foreign_key :labels, :projects, on_delete: :cascade
+    add_foreign_key :lists, :labels, on_delete: :cascade
+    add_foreign_key :label_links, :labels, on_delete: :cascade
+    add_foreign_key :milestones, :projects, on_delete: :cascade
+    add_foreign_key :snippets, :projects, on_delete: :cascade
+    add_foreign_key :web_hooks, :projects, on_delete: :cascade
+    add_foreign_key :protected_branch_merge_access_levels, :protected_branches, on_delete: :cascade
+    add_foreign_key :protected_branch_push_access_levels, :protected_branches, on_delete: :cascade
+    add_foreign_key :users_star_projects, :projects, on_delete: :cascade
+    add_foreign_key :releases, :projects, on_delete: :cascade
+    add_foreign_key :lfs_objects_projects, :projects, on_delete: :cascade
+    add_foreign_key :project_group_links, :projects, on_delete: :cascade
+    add_foreign_key :todos, :projects, on_delete: :cascade
+    add_foreign_key :deployments, :projects, on_delete: :cascade
+    add_foreign_key :environments, :projects, on_delete: :cascade
+    add_foreign_key :ci_builds, :projects, column: :gl_project_id, on_delete: :cascade
+    add_foreign_key :ci_runner_projects, :projects, column: :gl_project_id, on_delete: :cascade
+    add_foreign_key :ci_variables, :projects, column: :gl_project_id, on_delete: :cascade
+    add_foreign_key :ci_trigger_requests, :ci_triggers, column: :trigger_id, on_delete: :cascade
+    add_foreign_key :ci_triggers, :projects, column: :gl_project_id, on_delete: :cascade
+    add_foreign_key :ci_trigger_requests, :ci_commits, column: :commit_id, on_delete: :cascade
+    add_foreign_key :ci_commits, :projects, column: :gl_project_id, on_delete: :cascade
+    add_foreign_key :project_features, :projects, on_delete: :cascade
+    add_foreign_key :project_import_data, :projects, on_delete: :cascade
+  end
+
+  def down
+    remove_foreign_key :merge_requests, column: :target_project_id
+    remove_foreign_key :forked_project_links, column: :forked_to_project_id
+    remove_foreign_key :boards, :projects
+    remove_foreign_key :lists, :boards
+    remove_foreign_key :services, :projects
+    remove_foreign_key :notes, :projects
+    remove_foreign_key :events, :projects
+    remove_foreign_key :todos, :notes
+    remove_foreign_key :merge_request_diffs, :merge_requests
+    remove_foreign_key :issues, :projects
+    remove_foreign_key :labels, :projects
+    remove_foreign_key :lists, :labels
+    remove_foreign_key :label_links, :labels
+    remove_foreign_key :milestones, :projects
+    remove_foreign_key :snippets, :projects
+    remove_foreign_key :web_hooks, :projects
+    remove_foreign_key :protected_branch_merge_access_levels, :protected_branches
+    remove_foreign_key :protected_branch_push_access_levels, :protected_branches
+    remove_foreign_key :users_star_projects, :projects
+    remove_foreign_key :releases, :projects
+    remove_foreign_key :lfs_objects_projects, :projects
+    remove_foreign_key :project_group_links, :projects
+    remove_foreign_key :todos, :projects
+    remove_foreign_key :deployments, :projects
+    remove_foreign_key :environments, :projects
+    remove_foreign_key :ci_builds, column: :gl_project_id
+    remove_foreign_key :ci_runner_projects, column: :gl_project_id
+    remove_foreign_key :ci_variables, column: :gl_project_id
+    remove_foreign_key :ci_trigger_requests, column: :trigger_id
+    remove_foreign_key :ci_triggers, column: :gl_project_id
+    remove_foreign_key :ci_trigger_requests, column: :commit_id
+    remove_foreign_key :ci_commits, column: :gl_project_id
+    remove_foreign_key :project_features, :projects
+    remove_foreign_key :project_import_data, :projects
+
+    add_foreign_key :boards, :projects
+    add_foreign_key :lists, :boards
+    add_foreign_key :lists, :labels
+    add_foreign_key :protected_branch_merge_access_levels, :protected_branches
+    add_foreign_key :protected_branch_push_access_levels, :protected_branches
+  end
+
+  def remove_foreign_key(*args)
+    super(*args)
+  rescue ArgumentError
+    # Ignore if the foreign key doesn't exists
+  end
+
+  private
+
+  def delete_project_orphans(table_name, reference_column)
+    # select all soft-deleted issuables with no matching project
+    select_query = <<-EOF
+SELECT id FROM #{table_name}
+WHERE NOT EXISTS (SELECT 1 FROM projects WHERE projects.id = #{table_name}.#{reference_column})
+LIMIT 50000
+EOF
+
+    # Eeach thread is a new session, so we must dissable statement timeout
+    # on each of them. Also notice that these queries are executed in a new
+    # transaction. That should be ok, since we are removing orphan records.
+    Thread.new do
+      transaction do
+        disable_statement_timeout
+
+        # MySQL doesn't allow you to use the table from the DELETE in the subquery.
+        # You can however use the table in a subquery inside the subquery (see
+        # http://www.mysqlfaqs.net/mysql-errors/1093-you-can-not-specify-target-table-comments-for-update-in-from-clause),
+        # which seems to be perfectly fine. What's the point of the restriction then, you ask? Beats me.
+        loop do
+          deleted = delete "DELETE FROM #{table_name} WHERE id IN (SELECT id FROM (#{select_query}) AS t)"
+          break if deleted == 0
+        end
+      end
+    end
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
index 0d510c8a269f..ea7c4685b8a8 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -1258,23 +1258,52 @@
 
   add_index "web_hooks", ["project_id"], name: "index_web_hooks_on_project_id", using: :btree
 
-  add_foreign_key "boards", "projects"
+  add_foreign_key "boards", "projects", on_delete: :cascade
+  add_foreign_key "ci_builds", "projects", column: "gl_project_id", on_delete: :cascade
+  add_foreign_key "ci_commits", "projects", column: "gl_project_id", on_delete: :cascade
+  add_foreign_key "ci_runner_projects", "projects", column: "gl_project_id", on_delete: :cascade
+  add_foreign_key "ci_trigger_requests", "ci_commits", column: "commit_id", on_delete: :cascade
+  add_foreign_key "ci_trigger_requests", "ci_triggers", column: "trigger_id", on_delete: :cascade
+  add_foreign_key "ci_triggers", "projects", column: "gl_project_id", on_delete: :cascade
+  add_foreign_key "ci_variables", "projects", column: "gl_project_id", on_delete: :cascade
+  add_foreign_key "deployments", "projects", on_delete: :cascade
+  add_foreign_key "environments", "projects", on_delete: :cascade
+  add_foreign_key "events", "projects", on_delete: :cascade
+  add_foreign_key "forked_project_links", "projects", column: "forked_to_project_id", on_delete: :cascade
   add_foreign_key "issue_metrics", "issues", on_delete: :cascade
+  add_foreign_key "issues", "projects", on_delete: :cascade
+  add_foreign_key "label_links", "labels", on_delete: :cascade
   add_foreign_key "label_priorities", "labels", on_delete: :cascade
   add_foreign_key "label_priorities", "projects", on_delete: :cascade
   add_foreign_key "labels", "namespaces", column: "group_id", on_delete: :cascade
-  add_foreign_key "lists", "boards"
-  add_foreign_key "lists", "labels"
+  add_foreign_key "labels", "projects", on_delete: :cascade
+  add_foreign_key "lfs_objects_projects", "projects", on_delete: :cascade
+  add_foreign_key "lists", "boards", on_delete: :cascade
+  add_foreign_key "lists", "labels", on_delete: :cascade
+  add_foreign_key "merge_request_diffs", "merge_requests", on_delete: :cascade
   add_foreign_key "merge_request_metrics", "ci_commits", column: "pipeline_id", on_delete: :cascade
   add_foreign_key "merge_request_metrics", "merge_requests", on_delete: :cascade
+  add_foreign_key "merge_requests", "projects", column: "target_project_id", on_delete: :cascade
   add_foreign_key "merge_requests_closing_issues", "issues", on_delete: :cascade
   add_foreign_key "merge_requests_closing_issues", "merge_requests", on_delete: :cascade
+  add_foreign_key "milestones", "projects", on_delete: :cascade
+  add_foreign_key "notes", "projects", on_delete: :cascade
   add_foreign_key "personal_access_tokens", "users"
   add_foreign_key "project_authorizations", "projects", on_delete: :cascade
   add_foreign_key "project_authorizations", "users", on_delete: :cascade
-  add_foreign_key "protected_branch_merge_access_levels", "protected_branches"
-  add_foreign_key "protected_branch_push_access_levels", "protected_branches"
+  add_foreign_key "project_features", "projects", on_delete: :cascade
+  add_foreign_key "project_group_links", "projects", on_delete: :cascade
+  add_foreign_key "project_import_data", "projects", on_delete: :cascade
+  add_foreign_key "protected_branch_merge_access_levels", "protected_branches", on_delete: :cascade
+  add_foreign_key "protected_branch_push_access_levels", "protected_branches", on_delete: :cascade
+  add_foreign_key "releases", "projects", on_delete: :cascade
+  add_foreign_key "services", "projects", on_delete: :cascade
+  add_foreign_key "snippets", "projects", on_delete: :cascade
   add_foreign_key "subscriptions", "projects", on_delete: :cascade
+  add_foreign_key "todos", "notes", on_delete: :cascade
+  add_foreign_key "todos", "projects", on_delete: :cascade
   add_foreign_key "trending_projects", "projects", on_delete: :cascade
   add_foreign_key "u2f_registrations", "users"
+  add_foreign_key "users_star_projects", "projects", on_delete: :cascade
+  add_foreign_key "web_hooks", "projects", on_delete: :cascade
 end
diff --git a/spec/models/board_spec.rb b/spec/models/board_spec.rb
index 12d29540137c..28ba51fb5106 100644
--- a/spec/models/board_spec.rb
+++ b/spec/models/board_spec.rb
@@ -3,7 +3,7 @@
 describe Board do
   describe 'relationships' do
     it { is_expected.to belong_to(:project) }
-    it { is_expected.to have_many(:lists).order(list_type: :asc, position: :asc).dependent(:delete_all) }
+    it { is_expected.to have_many(:lists).order(list_type: :asc, position: :asc) }
   end
 
   describe 'validations' do
diff --git a/spec/models/concerns/issuable_spec.rb b/spec/models/concerns/issuable_spec.rb
index 6f84bffe046f..f656c0ae2fc8 100644
--- a/spec/models/concerns/issuable_spec.rb
+++ b/spec/models/concerns/issuable_spec.rb
@@ -134,7 +134,7 @@
   end
 
   describe "#sort" do
-    let(:project) { build_stubbed(:empty_project) }
+    let(:project) { create(:empty_project) }
 
     context "by milestone due date" do
       # Correct order is:
diff --git a/spec/models/forked_project_link_spec.rb b/spec/models/forked_project_link_spec.rb
index 1863581f57be..6f4af8366fd4 100644
--- a/spec/models/forked_project_link_spec.rb
+++ b/spec/models/forked_project_link_spec.rb
@@ -37,11 +37,6 @@
   it "project_from is not forked" do
     expect(project_from.forked?).to be_falsey
   end
-
-  it "project_to.destroy destroys fork_link" do
-    expect(forked_project_link).to receive(:destroy)
-    project_to.destroy
-  end
 end
 
 def fork_project(from_project, user)
diff --git a/spec/models/label_spec.rb b/spec/models/label_spec.rb
index 0c163659a717..dd9942f95bc1 100644
--- a/spec/models/label_spec.rb
+++ b/spec/models/label_spec.rb
@@ -8,8 +8,8 @@
 
   describe 'associations' do
     it { is_expected.to have_many(:issues).through(:label_links).source(:target) }
-    it { is_expected.to have_many(:label_links).dependent(:destroy) }
-    it { is_expected.to have_many(:lists).dependent(:destroy) }
+    it { is_expected.to have_many(:label_links) }
+    it { is_expected.to have_many(:lists) }
     it { is_expected.to have_many(:priorities).class_name('LabelPriority') }
   end
 
diff --git a/spec/models/merge_request_spec.rb b/spec/models/merge_request_spec.rb
index 2cc818af6c75..84b5551d84b1 100644
--- a/spec/models/merge_request_spec.rb
+++ b/spec/models/merge_request_spec.rb
@@ -9,7 +9,7 @@
     it { is_expected.to belong_to(:target_project).class_name('Project') }
     it { is_expected.to belong_to(:source_project).class_name('Project') }
     it { is_expected.to belong_to(:merge_user).class_name("User") }
-    it { is_expected.to have_many(:merge_request_diffs).dependent(:destroy) }
+    it { is_expected.to have_many(:merge_request_diffs) }
   end
 
   describe 'modules' do
diff --git a/spec/models/note_spec.rb b/spec/models/note_spec.rb
index 17a15b12dcb0..89bb392f2be3 100644
--- a/spec/models/note_spec.rb
+++ b/spec/models/note_spec.rb
@@ -8,7 +8,7 @@
     it { is_expected.to belong_to(:noteable).touch(true) }
     it { is_expected.to belong_to(:author).class_name('User') }
 
-    it { is_expected.to have_many(:todos).dependent(:destroy) }
+    it { is_expected.to have_many(:todos) }
   end
 
   describe 'modules' do
diff --git a/spec/models/project_spec.rb b/spec/models/project_spec.rb
index 8abcce42ce05..6f149f88fe25 100644
--- a/spec/models/project_spec.rb
+++ b/spec/models/project_spec.rb
@@ -7,48 +7,48 @@
     it { is_expected.to belong_to(:creator).class_name('User') }
     it { is_expected.to have_many(:users) }
     it { is_expected.to have_many(:services) }
-    it { is_expected.to have_many(:events).dependent(:destroy) }
-    it { is_expected.to have_many(:merge_requests).dependent(:destroy) }
-    it { is_expected.to have_many(:issues).dependent(:destroy) }
-    it { is_expected.to have_many(:milestones).dependent(:destroy) }
+    it { is_expected.to have_many(:events) }
+    it { is_expected.to have_many(:merge_requests) }
+    it { is_expected.to have_many(:issues) }
+    it { is_expected.to have_many(:milestones) }
     it { is_expected.to have_many(:project_members).dependent(:destroy) }
     it { is_expected.to have_many(:users).through(:project_members) }
     it { is_expected.to have_many(:requesters).dependent(:destroy) }
-    it { is_expected.to have_many(:notes).dependent(:destroy) }
-    it { is_expected.to have_many(:snippets).class_name('ProjectSnippet').dependent(:destroy) }
+    it { is_expected.to have_many(:notes) }
+    it { is_expected.to have_many(:snippets).class_name('ProjectSnippet') }
     it { is_expected.to have_many(:deploy_keys_projects).dependent(:destroy) }
     it { is_expected.to have_many(:deploy_keys) }
-    it { is_expected.to have_many(:hooks).dependent(:destroy) }
-    it { is_expected.to have_many(:protected_branches).dependent(:destroy) }
+    it { is_expected.to have_many(:hooks) }
+    it { is_expected.to have_many(:protected_branches) }
     it { is_expected.to have_many(:chat_services) }
-    it { is_expected.to have_one(:forked_project_link).dependent(:destroy) }
-    it { is_expected.to have_one(:slack_service).dependent(:destroy) }
-    it { is_expected.to have_one(:pushover_service).dependent(:destroy) }
-    it { is_expected.to have_one(:asana_service).dependent(:destroy) }
-    it { is_expected.to have_many(:boards).dependent(:destroy) }
-    it { is_expected.to have_one(:campfire_service).dependent(:destroy) }
-    it { is_expected.to have_one(:drone_ci_service).dependent(:destroy) }
-    it { is_expected.to have_one(:emails_on_push_service).dependent(:destroy) }
-    it { is_expected.to have_one(:builds_email_service).dependent(:destroy) }
-    it { is_expected.to have_one(:emails_on_push_service).dependent(:destroy) }
-    it { is_expected.to have_one(:irker_service).dependent(:destroy) }
-    it { is_expected.to have_one(:pivotaltracker_service).dependent(:destroy) }
-    it { is_expected.to have_one(:hipchat_service).dependent(:destroy) }
-    it { is_expected.to have_one(:flowdock_service).dependent(:destroy) }
-    it { is_expected.to have_one(:assembla_service).dependent(:destroy) }
-    it { is_expected.to have_one(:mattermost_slash_commands_service).dependent(:destroy) }
-    it { is_expected.to have_one(:gemnasium_service).dependent(:destroy) }
-    it { is_expected.to have_one(:buildkite_service).dependent(:destroy) }
-    it { is_expected.to have_one(:bamboo_service).dependent(:destroy) }
-    it { is_expected.to have_one(:teamcity_service).dependent(:destroy) }
-    it { is_expected.to have_one(:jira_service).dependent(:destroy) }
-    it { is_expected.to have_one(:redmine_service).dependent(:destroy) }
-    it { is_expected.to have_one(:custom_issue_tracker_service).dependent(:destroy) }
-    it { is_expected.to have_one(:bugzilla_service).dependent(:destroy) }
-    it { is_expected.to have_one(:gitlab_issue_tracker_service).dependent(:destroy) }
-    it { is_expected.to have_one(:external_wiki_service).dependent(:destroy) }
-    it { is_expected.to have_one(:project_feature).dependent(:destroy) }
-    it { is_expected.to have_one(:import_data).class_name('ProjectImportData').dependent(:destroy) }
+    it { is_expected.to have_one(:forked_project_link) }
+    it { is_expected.to have_one(:slack_service) }
+    it { is_expected.to have_one(:pushover_service) }
+    it { is_expected.to have_one(:asana_service) }
+    it { is_expected.to have_many(:boards) }
+    it { is_expected.to have_one(:campfire_service) }
+    it { is_expected.to have_one(:drone_ci_service) }
+    it { is_expected.to have_one(:emails_on_push_service) }
+    it { is_expected.to have_one(:builds_email_service) }
+    it { is_expected.to have_one(:emails_on_push_service) }
+    it { is_expected.to have_one(:irker_service) }
+    it { is_expected.to have_one(:pivotaltracker_service) }
+    it { is_expected.to have_one(:hipchat_service) }
+    it { is_expected.to have_one(:flowdock_service) }
+    it { is_expected.to have_one(:assembla_service) }
+    it { is_expected.to have_one(:mattermost_slash_commands_service) }
+    it { is_expected.to have_one(:gemnasium_service) }
+    it { is_expected.to have_one(:buildkite_service) }
+    it { is_expected.to have_one(:bamboo_service) }
+    it { is_expected.to have_one(:teamcity_service) }
+    it { is_expected.to have_one(:jira_service) }
+    it { is_expected.to have_one(:redmine_service) }
+    it { is_expected.to have_one(:custom_issue_tracker_service) }
+    it { is_expected.to have_one(:bugzilla_service) }
+    it { is_expected.to have_one(:gitlab_issue_tracker_service) }
+    it { is_expected.to have_one(:external_wiki_service) }
+    it { is_expected.to have_one(:project_feature) }
+    it { is_expected.to have_one(:import_data).class_name('ProjectImportData') }
     it { is_expected.to have_one(:last_event).class_name('Event') }
     it { is_expected.to have_one(:forked_from_project).through(:forked_project_link) }
     it { is_expected.to have_many(:commit_statuses) }
@@ -58,15 +58,15 @@
     it { is_expected.to have_many(:runners) }
     it { is_expected.to have_many(:variables) }
     it { is_expected.to have_many(:triggers) }
-    it { is_expected.to have_many(:labels).class_name('ProjectLabel').dependent(:destroy) }
-    it { is_expected.to have_many(:users_star_projects).dependent(:destroy) }
-    it { is_expected.to have_many(:environments).dependent(:destroy) }
-    it { is_expected.to have_many(:deployments).dependent(:destroy) }
-    it { is_expected.to have_many(:todos).dependent(:destroy) }
-    it { is_expected.to have_many(:releases).dependent(:destroy) }
-    it { is_expected.to have_many(:lfs_objects_projects).dependent(:destroy) }
-    it { is_expected.to have_many(:project_group_links).dependent(:destroy) }
-    it { is_expected.to have_many(:notification_settings).dependent(:destroy) }
+    it { is_expected.to have_many(:labels).class_name('ProjectLabel') }
+    it { is_expected.to have_many(:users_star_projects) }
+    it { is_expected.to have_many(:environments) }
+    it { is_expected.to have_many(:deployments) }
+    it { is_expected.to have_many(:todos) }
+    it { is_expected.to have_many(:releases) }
+    it { is_expected.to have_many(:lfs_objects_projects) }
+    it { is_expected.to have_many(:project_group_links) }
+    it { is_expected.to have_many(:notification_settings) }
     it { is_expected.to have_many(:forks).through(:forked_project_links) }
 
     context 'after initialized' do
-- 
GitLab


From 7bab3411c4eadd5f0e5efd9946e9e80b5f75e31a Mon Sep 17 00:00:00 2001
From: Yorick Peterse <yorickpeterse@gmail.com>
Date: Thu, 17 Nov 2016 17:19:17 +0100
Subject: [PATCH 2/5] Fix concurrency in foreign key migration

This ensures the orphan removal queries take care of all tables and can
truly run in parallel.
---
 ...514_add_foreign_keys_for_cascade_delete.rb | 169 ++++++++----------
 db/schema.rb                                  |   1 -
 2 files changed, 77 insertions(+), 93 deletions(-)

diff --git a/db/migrate/20160907201514_add_foreign_keys_for_cascade_delete.rb b/db/migrate/20160907201514_add_foreign_keys_for_cascade_delete.rb
index b2e4f5c34993..8aaeae905097 100644
--- a/db/migrate/20160907201514_add_foreign_keys_for_cascade_delete.rb
+++ b/db/migrate/20160907201514_add_foreign_keys_for_cascade_delete.rb
@@ -7,6 +7,7 @@ class AddForeignKeysForCascadeDelete < ActiveRecord::Migration
   disable_ddl_transaction!
 
   DOWNTIME = true
+
   DOWNTIME_REASON = <<-HEREDOC
     According to https://gocardless.com/blog/zero-downtime-postgres-migrations-the-hard-parts
     adding a foreign key needs to acquire an AccessExclusive lock. Since we're
@@ -14,6 +15,42 @@ class AddForeignKeysForCascadeDelete < ActiveRecord::Migration
     downtime.
   HEREDOC
 
+  TABLES = [
+    [:boards, :projects, :project_id],
+    [:ci_commits, :projects, :gl_project_id],
+    [:ci_runner_projects, :projects, :gl_project_id],
+    [:ci_trigger_requests, :ci_commits, :commit_id],
+    [:ci_trigger_requests, :ci_triggers, :trigger_id],
+    [:ci_triggers, :projects, :gl_project_id],
+    [:ci_variables, :projects, :gl_project_id],
+    [:deployments, :projects, :project_id],
+    [:environments, :projects, :project_id],
+    [:events, :projects, :project_id],
+    [:forked_project_links, :projects, :forked_to_project_id],
+    [:issues, :projects, :project_id],
+    [:label_links, :labels, :label_id],
+    [:labels, :projects, :project_id],
+    [:lfs_objects_projects, :projects, :project_id],
+    [:lists, :boards, :board_id],
+    [:lists, :labels, :label_id],
+    [:merge_requests, :projects, :target_project_id],
+    [:merge_request_diffs, :merge_requests, :merge_request_id],
+    [:milestones, :projects, :project_id],
+    [:notes, :projects, :project_id],
+    [:project_features, :projects, :project_id],
+    [:project_group_links, :projects, :project_id],
+    [:project_import_data, :projects, :project_id],
+    [:protected_branch_merge_access_levels, :protected_branches, :protected_branch_id],
+    [:protected_branch_push_access_levels, :protected_branches, :protected_branch_id],
+    [:releases, :projects, :project_id],
+    [:services, :projects, :project_id],
+    [:snippets, :projects, :project_id],
+    [:todos, :notes, :note_id],
+    [:todos, :projects, :project_id],
+    [:users_star_projects, :projects, :project_id],
+    [:web_hooks, :projects, :project_id],
+  ]
+
   def up
     disable_statement_timeout
 
@@ -34,14 +71,31 @@ def up
     remove_index :events, :target_id if index_exists?(:events, :target_id)
     remove_index :events, :target_type if index_exists?(:events, :target_type)
 
-    threads = []
-    threads << delete_project_orphans(:events, :project_id)
-    threads << delete_project_orphans(:issues, :project_id)
-    threads << delete_project_orphans(:merge_requests, :target_project_id)
-    threads << delete_project_orphans(:forked_project_links, :forked_to_project_id)
-    threads << delete_project_orphans(:services, :project_id)
-    threads << delete_project_orphans(:notes, :project_id)
-    threads.map(&:join)
+    # These already exist but don't specify on_delete: cascade, so we'll re-add them.
+    remove_foreign_key :boards, :projects
+    remove_foreign_key :lists, :boards
+    remove_foreign_key :lists, :labels
+    remove_foreign_key :protected_branch_merge_access_levels, :protected_branches
+    remove_foreign_key :protected_branch_push_access_levels, :protected_branches
+
+    TABLES.each_slice(8) do |slice|
+      threads = slice.map do |(source_table, target_table, column)|
+        Thread.new do
+          delete_project_orphans(source_table, target_table, column)
+        end
+      end
+
+      threads.each(&:join)
+
+      # Foreign keys can not be added in parallel as Rails' constraint name
+      # generation code is not thread-safe.
+      slice.each do |(source_table, target_table, column)|
+        add_foreign_key(source_table,
+                        target_table,
+                        column: column,
+                        on_delete: :cascade)
+      end
+    end
 
     # Rebuild indexes
     add_index :issues, [:project_id, :iid], unique: true
@@ -59,89 +113,14 @@ def up
     add_index :events, :project_id
     add_index :events, :target_id
     add_index :events, :target_type
-
-    # These already exist but don't specify on_delete: cascade, so we'll re-add them.
-    remove_foreign_key :boards, :projects
-    remove_foreign_key :lists, :boards
-    remove_foreign_key :lists, :labels
-    remove_foreign_key :protected_branch_merge_access_levels, :protected_branches
-    remove_foreign_key :protected_branch_push_access_levels, :protected_branches
-
-    # Merge Requests for target project and should be removed with it.
-    # Merge Requests from source project should be kept when source project was removed.
-    add_foreign_key :merge_requests, :projects, column: :target_project_id, on_delete: :cascade
-
-    add_foreign_key :forked_project_links, :projects, column: :forked_to_project_id, on_delete: :cascade
-    add_foreign_key :boards, :projects, on_delete: :cascade
-    add_foreign_key :lists, :boards, on_delete: :cascade
-    add_foreign_key :services, :projects, on_delete: :cascade
-    add_foreign_key :notes, :projects, on_delete: :cascade
-    add_foreign_key :events, :projects, on_delete: :cascade
-    add_foreign_key :todos, :notes, on_delete: :cascade
-    add_foreign_key :merge_request_diffs, :merge_requests, on_delete: :cascade
-    add_foreign_key :issues, :projects, on_delete: :cascade
-    add_foreign_key :labels, :projects, on_delete: :cascade
-    add_foreign_key :lists, :labels, on_delete: :cascade
-    add_foreign_key :label_links, :labels, on_delete: :cascade
-    add_foreign_key :milestones, :projects, on_delete: :cascade
-    add_foreign_key :snippets, :projects, on_delete: :cascade
-    add_foreign_key :web_hooks, :projects, on_delete: :cascade
-    add_foreign_key :protected_branch_merge_access_levels, :protected_branches, on_delete: :cascade
-    add_foreign_key :protected_branch_push_access_levels, :protected_branches, on_delete: :cascade
-    add_foreign_key :users_star_projects, :projects, on_delete: :cascade
-    add_foreign_key :releases, :projects, on_delete: :cascade
-    add_foreign_key :lfs_objects_projects, :projects, on_delete: :cascade
-    add_foreign_key :project_group_links, :projects, on_delete: :cascade
-    add_foreign_key :todos, :projects, on_delete: :cascade
-    add_foreign_key :deployments, :projects, on_delete: :cascade
-    add_foreign_key :environments, :projects, on_delete: :cascade
-    add_foreign_key :ci_builds, :projects, column: :gl_project_id, on_delete: :cascade
-    add_foreign_key :ci_runner_projects, :projects, column: :gl_project_id, on_delete: :cascade
-    add_foreign_key :ci_variables, :projects, column: :gl_project_id, on_delete: :cascade
-    add_foreign_key :ci_trigger_requests, :ci_triggers, column: :trigger_id, on_delete: :cascade
-    add_foreign_key :ci_triggers, :projects, column: :gl_project_id, on_delete: :cascade
-    add_foreign_key :ci_trigger_requests, :ci_commits, column: :commit_id, on_delete: :cascade
-    add_foreign_key :ci_commits, :projects, column: :gl_project_id, on_delete: :cascade
-    add_foreign_key :project_features, :projects, on_delete: :cascade
-    add_foreign_key :project_import_data, :projects, on_delete: :cascade
   end
 
   def down
-    remove_foreign_key :merge_requests, column: :target_project_id
-    remove_foreign_key :forked_project_links, column: :forked_to_project_id
-    remove_foreign_key :boards, :projects
-    remove_foreign_key :lists, :boards
-    remove_foreign_key :services, :projects
-    remove_foreign_key :notes, :projects
-    remove_foreign_key :events, :projects
-    remove_foreign_key :todos, :notes
-    remove_foreign_key :merge_request_diffs, :merge_requests
-    remove_foreign_key :issues, :projects
-    remove_foreign_key :labels, :projects
-    remove_foreign_key :lists, :labels
-    remove_foreign_key :label_links, :labels
-    remove_foreign_key :milestones, :projects
-    remove_foreign_key :snippets, :projects
-    remove_foreign_key :web_hooks, :projects
-    remove_foreign_key :protected_branch_merge_access_levels, :protected_branches
-    remove_foreign_key :protected_branch_push_access_levels, :protected_branches
-    remove_foreign_key :users_star_projects, :projects
-    remove_foreign_key :releases, :projects
-    remove_foreign_key :lfs_objects_projects, :projects
-    remove_foreign_key :project_group_links, :projects
-    remove_foreign_key :todos, :projects
-    remove_foreign_key :deployments, :projects
-    remove_foreign_key :environments, :projects
-    remove_foreign_key :ci_builds, column: :gl_project_id
-    remove_foreign_key :ci_runner_projects, column: :gl_project_id
-    remove_foreign_key :ci_variables, column: :gl_project_id
-    remove_foreign_key :ci_trigger_requests, column: :trigger_id
-    remove_foreign_key :ci_triggers, column: :gl_project_id
-    remove_foreign_key :ci_trigger_requests, column: :commit_id
-    remove_foreign_key :ci_commits, column: :gl_project_id
-    remove_foreign_key :project_features, :projects
-    remove_foreign_key :project_import_data, :projects
+    TABLES.each do |(source_table, target_table, column)|
+      remove_foreign_key(source_table, target_table, column: column)
+    end
 
+    # Re-add these without a cascading delete.
     add_foreign_key :boards, :projects
     add_foreign_key :lists, :boards
     add_foreign_key :lists, :labels
@@ -157,11 +136,15 @@ def remove_foreign_key(*args)
 
   private
 
-  def delete_project_orphans(table_name, reference_column)
+  def delete_project_orphans(source_table, target_table, reference_column)
     # select all soft-deleted issuables with no matching project
     select_query = <<-EOF
-SELECT id FROM #{table_name}
-WHERE NOT EXISTS (SELECT 1 FROM projects WHERE projects.id = #{table_name}.#{reference_column})
+SELECT id FROM #{source_table}
+WHERE NOT EXISTS (
+    SELECT 1
+    FROM #{target_table}
+    WHERE #{target_table}.id = #{source_table}.#{reference_column}
+)
 LIMIT 50000
 EOF
 
@@ -169,7 +152,9 @@ def delete_project_orphans(table_name, reference_column)
     # on each of them. Also notice that these queries are executed in a new
     # transaction. That should be ok, since we are removing orphan records.
     Thread.new do
-      transaction do
+      connection = ActiveRecord::Base.connection
+
+      connection.transaction do
         disable_statement_timeout
 
         # MySQL doesn't allow you to use the table from the DELETE in the subquery.
@@ -177,7 +162,7 @@ def delete_project_orphans(table_name, reference_column)
         # http://www.mysqlfaqs.net/mysql-errors/1093-you-can-not-specify-target-table-comments-for-update-in-from-clause),
         # which seems to be perfectly fine. What's the point of the restriction then, you ask? Beats me.
         loop do
-          deleted = delete "DELETE FROM #{table_name} WHERE id IN (SELECT id FROM (#{select_query}) AS t)"
+          deleted = connection.delete "DELETE FROM #{source_table} WHERE id IN (SELECT id FROM (#{select_query}) AS t)"
           break if deleted == 0
         end
       end
diff --git a/db/schema.rb b/db/schema.rb
index ea7c4685b8a8..aa85b6722447 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -1259,7 +1259,6 @@
   add_index "web_hooks", ["project_id"], name: "index_web_hooks_on_project_id", using: :btree
 
   add_foreign_key "boards", "projects", on_delete: :cascade
-  add_foreign_key "ci_builds", "projects", column: "gl_project_id", on_delete: :cascade
   add_foreign_key "ci_commits", "projects", column: "gl_project_id", on_delete: :cascade
   add_foreign_key "ci_runner_projects", "projects", column: "gl_project_id", on_delete: :cascade
   add_foreign_key "ci_trigger_requests", "ci_commits", column: "commit_id", on_delete: :cascade
-- 
GitLab


From ed36c5e6a3a7fccf02fd82565248a58192e765af Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Alejandro=20Rodr=C3=ADguez?= <alejorro70@gmail.com>
Date: Tue, 29 Nov 2016 13:53:10 -0300
Subject: [PATCH 3/5] Fix existing foreign keys removal in migration

---
 .../20160907201514_add_foreign_keys_for_cascade_delete.rb | 8 +-------
 1 file changed, 1 insertion(+), 7 deletions(-)

diff --git a/db/migrate/20160907201514_add_foreign_keys_for_cascade_delete.rb b/db/migrate/20160907201514_add_foreign_keys_for_cascade_delete.rb
index 8aaeae905097..1ad5e5061917 100644
--- a/db/migrate/20160907201514_add_foreign_keys_for_cascade_delete.rb
+++ b/db/migrate/20160907201514_add_foreign_keys_for_cascade_delete.rb
@@ -117,7 +117,7 @@ def up
 
   def down
     TABLES.each do |(source_table, target_table, column)|
-      remove_foreign_key(source_table, target_table, column: column)
+      remove_foreign_key(source_table, column: column)
     end
 
     # Re-add these without a cascading delete.
@@ -128,12 +128,6 @@ def down
     add_foreign_key :protected_branch_push_access_levels, :protected_branches
   end
 
-  def remove_foreign_key(*args)
-    super(*args)
-  rescue ArgumentError
-    # Ignore if the foreign key doesn't exists
-  end
-
   private
 
   def delete_project_orphans(source_table, target_table, reference_column)
-- 
GitLab


From 27b8208f659d4756178da127ace250d949b7dc6a Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Alejandro=20Rodr=C3=ADguez?= <alejorro70@gmail.com>
Date: Tue, 29 Nov 2016 13:53:35 -0300
Subject: [PATCH 4/5] Use `with_connection` to avoid connection leakage in
 threads, and concurrent index creation

---
 ...514_add_foreign_keys_for_cascade_delete.rb | 54 +++++++++----------
 1 file changed, 27 insertions(+), 27 deletions(-)

diff --git a/db/migrate/20160907201514_add_foreign_keys_for_cascade_delete.rb b/db/migrate/20160907201514_add_foreign_keys_for_cascade_delete.rb
index 1ad5e5061917..17654b97c6da 100644
--- a/db/migrate/20160907201514_add_foreign_keys_for_cascade_delete.rb
+++ b/db/migrate/20160907201514_add_foreign_keys_for_cascade_delete.rb
@@ -98,21 +98,21 @@ def up
     end
 
     # Rebuild indexes
-    add_index :issues, [:project_id, :iid], unique: true
-    add_index :issues, :assignee_id
-    add_index :issues, :author_id
-    add_index :issues, :confidential
-    add_index :issues, :created_at
-    add_index :issues, :deleted_at
-    add_index :issues, :due_date
-    add_index :issues, :milestone_id
-    add_index :issues, :state
-    add_index :events, :action
-    add_index :events, :author_id
-    add_index :events, :created_at
-    add_index :events, :project_id
-    add_index :events, :target_id
-    add_index :events, :target_type
+    add_concurrent_index :issues, [:project_id, :iid], unique: true
+    add_concurrent_index :issues, :assignee_id
+    add_concurrent_index :issues, :author_id
+    add_concurrent_index :issues, :confidential
+    add_concurrent_index :issues, :created_at
+    add_concurrent_index :issues, :deleted_at
+    add_concurrent_index :issues, :due_date
+    add_concurrent_index :issues, :milestone_id
+    add_concurrent_index :issues, :state
+    add_concurrent_index :events, :action
+    add_concurrent_index :events, :author_id
+    add_concurrent_index :events, :created_at
+    add_concurrent_index :events, :project_id
+    add_concurrent_index :events, :target_id
+    add_concurrent_index :events, :target_type
   end
 
   def down
@@ -146,18 +146,18 @@ def delete_project_orphans(source_table, target_table, reference_column)
     # on each of them. Also notice that these queries are executed in a new
     # transaction. That should be ok, since we are removing orphan records.
     Thread.new do
-      connection = ActiveRecord::Base.connection
-
-      connection.transaction do
-        disable_statement_timeout
-
-        # MySQL doesn't allow you to use the table from the DELETE in the subquery.
-        # You can however use the table in a subquery inside the subquery (see
-        # http://www.mysqlfaqs.net/mysql-errors/1093-you-can-not-specify-target-table-comments-for-update-in-from-clause),
-        # which seems to be perfectly fine. What's the point of the restriction then, you ask? Beats me.
-        loop do
-          deleted = connection.delete "DELETE FROM #{source_table} WHERE id IN (SELECT id FROM (#{select_query}) AS t)"
-          break if deleted == 0
+      ActiveRecord::Base.connection_pool.with_connection do |connection|
+        connection.transaction do
+          disable_statement_timeout
+
+          # MySQL doesn't allow you to use the table from the DELETE in the subquery.
+          # You can however use the table in a subquery inside the subquery (see
+          # http://www.mysqlfaqs.net/mysql-errors/1093-you-can-not-specify-target-table-comments-for-update-in-from-clause),
+          # which seems to be perfectly fine. What's the point of the restriction then, you ask? Beats me.
+          loop do
+            deleted = connection.delete "DELETE FROM #{source_table} WHERE id IN (SELECT id FROM (#{select_query}) AS t)"
+            break if deleted == 0
+          end
         end
       end
     end
-- 
GitLab


From a7fa7bc23813efedabd994f55fdc709066b1e9ab Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Alejandro=20Rodr=C3=ADguez?= <alejorro70@gmail.com>
Date: Tue, 6 Dec 2016 14:03:56 -0300
Subject: [PATCH 5/5] Rename foreign keys migration to fit into the migration
 timeline

---
 ...rb => 20161206201514_add_foreign_keys_for_cascade_delete.rb} | 0
 db/schema.rb                                                    | 2 +-
 2 files changed, 1 insertion(+), 1 deletion(-)
 rename db/migrate/{20160907201514_add_foreign_keys_for_cascade_delete.rb => 20161206201514_add_foreign_keys_for_cascade_delete.rb} (100%)

diff --git a/db/migrate/20160907201514_add_foreign_keys_for_cascade_delete.rb b/db/migrate/20161206201514_add_foreign_keys_for_cascade_delete.rb
similarity index 100%
rename from db/migrate/20160907201514_add_foreign_keys_for_cascade_delete.rb
rename to db/migrate/20161206201514_add_foreign_keys_for_cascade_delete.rb
diff --git a/db/schema.rb b/db/schema.rb
index aa85b6722447..3b64ba1aa1f0 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: 20161128161412) do
+ActiveRecord::Schema.define(version: 20161206201514) do
 
   # These are extensions that must be enabled in order to support this database
   enable_extension "plpgsql"
-- 
GitLab