summaryrefslogtreecommitdiff
path: root/db
diff options
context:
space:
mode:
authorAndreas Brandl <abrandl@gitlab.com>2018-05-15 22:20:45 +0200
committerAndreas Brandl <abrandl@gitlab.com>2018-05-21 17:56:57 +0200
commit82576518226843bcc104739eb01975036ce8a60f (patch)
tree6a3087169d48034eb6fb770e16cd8216ec4c2606 /db
parenta0c79f9d7025872fc2aa91805058739b26093989 (diff)
downloadgitlab-ce-82576518226843bcc104739eb01975036ce8a60f.tar.gz
Migration to add/drop primary key constraints for composite keys.ab-43706-composite-primary-keys
Closes #43706.
Diffstat (limited to 'db')
-rw-r--r--db/optional_migrations/composite_primary_keys.rb63
1 files changed, 63 insertions, 0 deletions
diff --git a/db/optional_migrations/composite_primary_keys.rb b/db/optional_migrations/composite_primary_keys.rb
new file mode 100644
index 00000000000..0fd3fca52dd
--- /dev/null
+++ b/db/optional_migrations/composite_primary_keys.rb
@@ -0,0 +1,63 @@
+# This migration adds a primary key constraint to tables
+# that only have a composite unique key.
+#
+# This is not strictly relevant to Rails (v4 does not
+# support composite primary keys). However this becomes
+# useful for e.g. PostgreSQL's logical replication (pglogical)
+# which requires all tables to have a primary key constraint.
+#
+# In that sense, the migration is optional and not strictly needed.
+class CompositePrimaryKeysMigration < ActiveRecord::Migration
+ include Gitlab::Database::MigrationHelpers
+
+ DOWNTIME = false
+
+ Index = Struct.new(:table, :name, :columns)
+
+ TABLES = [
+ Index.new(:issue_assignees, 'index_issue_assignees_on_issue_id_and_user_id', %i(issue_id user_id)),
+ Index.new(:user_interacted_projects, 'index_user_interacted_projects_on_project_id_and_user_id', %i(project_id user_id)),
+ Index.new(:merge_request_diff_files, 'index_merge_request_diff_files_on_mr_diff_id_and_order', %i(merge_request_diff_id relative_order)),
+ Index.new(:merge_request_diff_commits, 'index_merge_request_diff_commits_on_mr_diff_id_and_order', %i(merge_request_diff_id relative_order)),
+ Index.new(:project_authorizations, 'index_project_authorizations_on_user_id_project_id_access_level', %i(user_id project_id access_level)),
+ Index.new(:push_event_payloads, 'index_push_event_payloads_on_event_id', %i(event_id)),
+ Index.new(:schema_migrations, 'unique_schema_migrations', %(version)),
+ ]
+
+ disable_ddl_transaction!
+
+ def up
+ return unless Gitlab::Database.postgresql?
+
+ disable_statement_timeout
+ TABLES.each do |index|
+ add_primary_key(index)
+ end
+ end
+
+ def down
+ return unless Gitlab::Database.postgresql?
+
+ disable_statement_timeout
+ TABLES.each do |index|
+ remove_primary_key(index)
+ end
+ end
+
+ private
+ def add_primary_key(index)
+ execute "ALTER TABLE #{index.table} ADD PRIMARY KEY USING INDEX #{index.name}"
+ end
+
+ def remove_primary_key(index)
+ temp_index_name = "#{index.name[0..58]}_old"
+ rename_index index.table, index.name, temp_index_name if index_exists_by_name?(index.table, index.name)
+
+ # re-create unique key index
+ add_concurrent_index index.table, index.columns, unique: true, name: index.name
+
+ # This also drops the `temp_index_name` as this is owned by the constraint
+ execute "ALTER TABLE #{index.table} DROP CONSTRAINT IF EXISTS #{temp_index_name}"
+ end
+end
+