Migration to add primary key constraint for composite keys (!18980) · Merge requests · GitLab.org / GitLab FOSS

Andreas Brandl requested to merge ab-43706-composite-primary-keys into master May 15, 2018

What does this MR do?

This MR provides an (optional) migration that allows us to add/drop primary key constraints on table that only have a composite unique key constraint. This will be useful for example for using pglogical which requires primary keys on all tables.

Additionally, there's a normal migration to add NOT NULL constraints to project_authorizations. This is necessary because nullable columns must not be part of a primary key constraint (instead, PostgreSQL will mark them NOT NULL automatically). The schema issue is https://gitlab.com/gitlab-org/gitlab-ce/issues/32258.

Migration path (up / down)

== 20180517082340 AddNotNullConstraintsToProjectAuthorizations: migrating =====
-- execute("ALTER TABLE project_authorizations\n  ALTER COLUMN user_id SET NOT NULL,\n  ALTER COLUMN project_id SET NOT NULL,\n  ALTER COLUMN access_level SET NOT NULL\n")
   -> 0.0005s
== 20180517082340 AddNotNullConstraintsToProjectAuthorizations: migrated (0.0006s) 

-- execute("SET statement_timeout TO 0")
   -> 0.0085s
-- execute("ALTER TABLE issue_assignees ADD PRIMARY KEY USING INDEX index_issue_assignees_on_issue_id_and_user_id")
   -> 0.0102s
-- execute("ALTER TABLE user_interacted_projects ADD PRIMARY KEY USING INDEX index_user_interacted_projects_on_project_id_and_user_id")
   -> 0.0030s
-- execute("ALTER TABLE merge_request_diff_files ADD PRIMARY KEY USING INDEX index_merge_request_diff_files_on_mr_diff_id_and_order")
   -> 0.0028s
-- execute("ALTER TABLE merge_request_diff_commits ADD PRIMARY KEY USING INDEX index_merge_request_diff_commits_on_mr_diff_id_and_order")
   -> 0.0029s
-- execute("ALTER TABLE project_authorizations ADD PRIMARY KEY USING INDEX index_project_authorizations_on_user_id_project_id_access_level")
   -> 0.0028s
-- execute("ALTER TABLE push_event_payloads ADD PRIMARY KEY USING INDEX index_push_event_payloads_on_event_id")
   -> 0.0031s
-- execute("ALTER TABLE schema_migrations ADD PRIMARY KEY USING INDEX unique_schema_migrations")
   -> 0.0034s

Down:

-- execute("SET statement_timeout TO 0")
   -> 0.0088s
-- indexes(:issue_assignees)
   -> 0.0035s
-- rename_index(:issue_assignees, "index_issue_assignees_on_issue_id_and_user_id", "index_issue_assignees_on_issue_id_and_user_id_old")
   -> 0.0019s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- index_exists?(:issue_assignees, [:issue_id, :user_id], {:unique=>true, :name=>"index_issue_assignees_on_issue_id_and_user_id", :algorithm=>:concurrently})
   -> 0.0010s
-- add_index(:issue_assignees, [:issue_id, :user_id], {:unique=>true, :name=>"index_issue_assignees_on_issue_id_and_user_id", :algorithm=>:concurrently})
   -> 0.0065s
-- execute("ALTER TABLE issue_assignees DROP CONSTRAINT IF EXISTS index_issue_assignees_on_issue_id_and_user_id_old")
   -> 0.0017s
-- indexes(:user_interacted_projects)
   -> 0.0011s
-- rename_index(:user_interacted_projects, "index_user_interacted_projects_on_project_id_and_user_id", "index_user_interacted_projects_on_project_id_and_user_id_old")
   -> 0.0015s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0003s
-- index_exists?(:user_interacted_projects, [:project_id, :user_id], {:unique=>true, :name=>"index_user_interacted_projects_on_project_id_and_user_id", :algorithm=>:concurrently})
   -> 0.0013s
-- add_index(:user_interacted_projects, [:project_id, :user_id], {:unique=>true, :name=>"index_user_interacted_projects_on_project_id_and_user_id", :algorithm=>:concurrently})
   -> 0.0072s
-- execute("ALTER TABLE user_interacted_projects DROP CONSTRAINT IF EXISTS index_user_interacted_projects_on_project_id_and_user_id_old")
   -> 0.0016s
-- indexes(:merge_request_diff_files)
   -> 0.0007s
-- rename_index(:merge_request_diff_files, "index_merge_request_diff_files_on_mr_diff_id_and_order", "index_merge_request_diff_files_on_mr_diff_id_and_order_old")
   -> 0.0015s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- index_exists?(:merge_request_diff_files, [:merge_request_diff_id, :relative_order], {:unique=>true, :name=>"index_merge_request_diff_files_on_mr_diff_id_and_order", :algorithm=>:concurrently})
   -> 0.0007s
-- add_index(:merge_request_diff_files, [:merge_request_diff_id, :relative_order], {:unique=>true, :name=>"index_merge_request_diff_files_on_mr_diff_id_and_order", :algorithm=>:concurrently})
   -> 0.0068s
-- execute("ALTER TABLE merge_request_diff_files DROP CONSTRAINT IF EXISTS index_merge_request_diff_files_on_mr_diff_id_and_order_old")
   -> 0.0015s
-- indexes(:merge_request_diff_commits)
   -> 0.0010s
-- rename_index(:merge_request_diff_commits, "index_merge_request_diff_commits_on_mr_diff_id_and_order", "index_merge_request_diff_commits_on_mr_diff_id_and_order_old")
   -> 0.0014s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- index_exists?(:merge_request_diff_commits, [:merge_request_diff_id, :relative_order], {:unique=>true, :name=>"index_merge_request_diff_commits_on_mr_diff_id_and_order", :algorithm=>:concurrently})
   -> 0.0009s
-- add_index(:merge_request_diff_commits, [:merge_request_diff_id, :relative_order], {:unique=>true, :name=>"index_merge_request_diff_commits_on_mr_diff_id_and_order", :algorithm=>:concurrently})
   -> 0.0074s
-- execute("ALTER TABLE merge_request_diff_commits DROP CONSTRAINT IF EXISTS index_merge_request_diff_commits_on_mr_diff_id_and_order_old")
   -> 0.0038s
-- indexes(:project_authorizations)
   -> 0.0048s
-- rename_index(:project_authorizations, "index_project_authorizations_on_user_id_project_id_access_level", "index_project_authorizations_on_user_id_project_id_access_l_old")
   -> 0.0026s
-- transaction_open?()
   -> 0.0001s
-- execute("SET statement_timeout TO 0")
   -> 0.0009s
-- index_exists?(:project_authorizations, [:user_id, :project_id, :access_level], {:unique=>true, :name=>"index_project_authorizations_on_user_id_project_id_access_level", :algorithm=>:concurrently})
   -> 0.0048s
-- add_index(:project_authorizations, [:user_id, :project_id, :access_level], {:unique=>true, :name=>"index_project_authorizations_on_user_id_project_id_access_level", :algorithm=>:concurrently})
   -> 0.0152s
-- execute("ALTER TABLE project_authorizations DROP CONSTRAINT IF EXISTS index_project_authorizations_on_user_id_project_id_access_l_old")
   -> 0.0026s
-- indexes(:push_event_payloads)
   -> 0.0015s
-- rename_index(:push_event_payloads, "index_push_event_payloads_on_event_id", "index_push_event_payloads_on_event_id_old")
   -> 0.0019s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- index_exists?(:push_event_payloads, [:event_id], {:unique=>true, :name=>"index_push_event_payloads_on_event_id", :algorithm=>:concurrently})
   -> 0.0012s
-- add_index(:push_event_payloads, [:event_id], {:unique=>true, :name=>"index_push_event_payloads_on_event_id", :algorithm=>:concurrently})
   -> 0.0084s
-- execute("ALTER TABLE push_event_payloads DROP CONSTRAINT IF EXISTS index_push_event_payloads_on_event_id_old")
   -> 0.0021s
-- indexes(:schema_migrations)
   -> 0.0013s
-- rename_index(:schema_migrations, "unique_schema_migrations", "unique_schema_migrations_old")
   -> 0.0019s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0006s
-- index_exists?(:schema_migrations, "version", {:unique=>true, :name=>"unique_schema_migrations", :algorithm=>:concurrently})
   -> 0.0017s
-- add_index(:schema_migrations, "version", {:unique=>true, :name=>"unique_schema_migrations", :algorithm=>:concurrently})
   -> 0.0102s
-- execute("ALTER TABLE schema_migrations DROP CONSTRAINT IF EXISTS unique_schema_migrations_old")
   -> 0.0024s

== 20180517082340 AddNotNullConstraintsToProjectAuthorizations: reverting =====
-- execute("ALTER TABLE project_authorizations\n  ALTER COLUMN user_id DROP NOT NULL,\n  ALTER COLUMN project_id DROP NOT NULL,\n  ALTER COLUMN access_level DROP NOT NULL\n")
   -> 0.0005s
== 20180517082340 AddNotNullConstraintsToProjectAuthorizations: reverted (0.0006s)

Does this MR meet the acceptance criteria?

Changelog entry added, if necessary
Documentation created/updated
Conform by the code review guidelines
- Has been reviewed by a Backend maintainer
- Has been reviewed by a Database specialist
Conform by the merge request performance guides
Conform by the style guides
If you have multiple commits, please combine them into a few logically organized commits by squashing them
End-to-end tests pass (package-and-qa manual pipeline job)

What are the relevant issue numbers?

https://gitlab.com/gitlab-org/gitlab-ce/issues/43706

Edited May 22, 2018 by Yorick Peterse

Admin message

Migration to add primary key constraint for composite keys

What does this MR do?

Migration path (up / down)

Does this MR meet the acceptance criteria?

What are the relevant issue numbers?

Merge request reports