Merge branch 'improve-notification-settings-migrations' into 'master'

Remove notification settings in batches

## What does this MR do?

This improves the performance of the migration `db/migrate/20160603180330_remove_duplicated_notification_settings.rb` by removing duplicate rows in batches instead of using a single big `DELETE FROM` query.

## Why was this MR needed?

The original migration would locally take 45 minutes to complete, possibly up to hours on GitLab.com and similar setups.

## What are the relevant issue numbers?

#18289

See merge request !4529

2 files changed, 31 insertions, 4 deletions

diff --git a/db/migrate/20160603180330_remove_duplicated_notification_settings.rb b/db/migrate/20160603180330_remove_duplicated_notification_settings.rb
index c2fcac4c53d..fe1c863b5b9 100644
--- a/db/migrate/20160603180330_remove_duplicated_notification_settings.rb
+++ b/db/migrate/20160603180330_remove_duplicated_notification_settings.rb
@@ -1,7 +1,32 @@
 class RemoveDuplicatedNotificationSettings < ActiveRecord::Migration
   def up
-    execute <<-SQL
-      DELETE FROM notification_settings WHERE id NOT IN ( SELECT min_id from (SELECT MIN(id) as min_id FROM notification_settings GROUP BY user_id, source_type, source_id) as dups )
-    SQL
+    duplicates = exec_query(%Q{
+      SELECT user_id, source_type, source_id
+      FROM notification_settings
+      GROUP BY user_id, source_type, source_id
+      HAVING COUNT(*) > 1
+    })
+
+    duplicates.each do |row|
+      uid = row['user_id']
+      stype = connection.quote(row['source_type'])
+      sid = row['source_id']
+
+      execute(%Q{
+        DELETE FROM notification_settings
+        WHERE user_id = #{uid}
+        AND source_type = #{stype}
+        AND source_id = #{sid}
+        AND id != (
+          SELECT id FROM (
+            SELECT min(id) AS id
+            FROM notification_settings
+            WHERE user_id = #{uid}
+            AND source_type = #{stype}
+            AND source_id = #{sid}
+          ) min_ids
+        )
+      })
+    end
   end
 end
diff --git a/db/schema.rb b/db/schema.rb
index 69e37470de0..00829d63b66 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -11,7 +11,8 @@
 #
 # It's strongly recommended that you check this file into your version control system.
 
-ActiveRecord::Schema.define(version: 20160530150109) do
+ActiveRecord::Schema.define(version: 20160603182247) do
+
   # These are extensions that must be enabled in order to support this database
   enable_extension "plpgsql"
   enable_extension "pg_trgm"
@@ -676,6 +677,7 @@ ActiveRecord::Schema.define(version: 20160530150109) do
   end
 
   add_index "notification_settings", ["source_id", "source_type"], name: "index_notification_settings_on_source_id_and_source_type", using: :btree
+  add_index "notification_settings", ["user_id", "source_id", "source_type"], name: "index_notifications_on_user_id_and_source_id_and_source_type", unique: true, using: :btree
   add_index "notification_settings", ["user_id"], name: "index_notification_settings_on_user_id", using: :btree
 
   create_table "oauth_access_grants", force: :cascade do |t|