Specify primary key for tables without
What does this MR do?
This change promotes existing unique indexes (covering NOT NULL columns) to the primary key, for tables that didn't have an explicit primary key before.
This is important, e.g. to support logical replication. See #251072 (closed) for more information.
For these tables, promoting the existing index is a metadata-only operation, which complete in database-labs in a few milli-seconds.
There are follow-ups for other tables, that are more problematic: #270090 (closed), #270093 (closed)
Upon promotion, the unique index will be renamed. For a rollback, we'll have to re-create the unique index and afterwards drop the primary key (which also drops the PK index).
Migration up
== 20201014163633 SpecifyPrimaryKeyWhereMissing: migrating ====================
-- execute("ALTER TABLE project_authorizations ADD CONSTRAINT project_authorizations_pkey PRIMARY KEY USING INDEX index_project_authorizations_on_user_id_project_id_access_level")
-> 0.0010s
-- execute("ALTER TABLE analytics_language_trend_repository_languages ADD CONSTRAINT analytics_language_trend_repository_languages_pkey PRIMARY KEY USING INDEX analytics_repository_languages_unique_index")
-> 0.0006s
-- execute("ALTER TABLE approval_project_rules_protected_branches ADD CONSTRAINT approval_project_rules_protected_branches_pkey PRIMARY KEY USING INDEX index_approval_project_rules_protected_branches_unique")
-> 0.0005s
-- execute("ALTER TABLE ci_build_trace_sections ADD CONSTRAINT ci_build_trace_sections_pkey PRIMARY KEY USING INDEX index_ci_build_trace_sections_on_build_id_and_section_name_id")
-> 0.0019s
-- execute("ALTER TABLE deployment_merge_requests ADD CONSTRAINT deployment_merge_requests_pkey PRIMARY KEY USING INDEX idx_deployment_merge_requests_unique_index")
-> 0.0016s
-- execute("ALTER TABLE issue_assignees ADD CONSTRAINT issue_assignees_pkey PRIMARY KEY USING INDEX index_issue_assignees_on_issue_id_and_user_id")
-> 0.0009s
-- execute("ALTER TABLE issues_prometheus_alert_events ADD CONSTRAINT issues_prometheus_alert_events_pkey PRIMARY KEY USING INDEX issue_id_prometheus_alert_event_id_index")
-> 0.0006s
-- execute("ALTER TABLE issues_self_managed_prometheus_alert_events ADD CONSTRAINT issues_self_managed_prometheus_alert_events_pkey PRIMARY KEY USING INDEX issue_id_self_managed_prometheus_alert_event_id_index")
-> 0.0007s
-- execute("ALTER TABLE merge_request_diff_commits ADD CONSTRAINT merge_request_diff_commits_pkey PRIMARY KEY USING INDEX index_merge_request_diff_commits_on_mr_diff_id_and_order")
-> 0.0012s
-- execute("ALTER TABLE merge_request_diff_files ADD CONSTRAINT merge_request_diff_files_pkey PRIMARY KEY USING INDEX index_merge_request_diff_files_on_mr_diff_id_and_order")
-> 0.0013s
-- execute("ALTER TABLE milestone_releases ADD CONSTRAINT milestone_releases_pkey PRIMARY KEY USING INDEX index_miletone_releases_on_milestone_and_release")
-> 0.0006s
-- execute("ALTER TABLE project_pages_metadata ADD CONSTRAINT project_pages_metadata_pkey PRIMARY KEY USING INDEX index_project_pages_metadata_on_project_id")
-> 0.0006s
-- execute("ALTER TABLE push_event_payloads ADD CONSTRAINT push_event_payloads_pkey PRIMARY KEY USING INDEX index_push_event_payloads_on_event_id")
-> 0.0005s
-- execute("ALTER TABLE repository_languages ADD CONSTRAINT repository_languages_pkey PRIMARY KEY USING INDEX index_repository_languages_on_project_and_languages_id")
-> 0.0017s
-- execute("ALTER TABLE user_interacted_projects ADD CONSTRAINT user_interacted_projects_pkey PRIMARY KEY USING INDEX index_user_interacted_projects_on_project_id_and_user_id")
-> 0.0016s
-- execute("ALTER TABLE users_security_dashboard_projects ADD CONSTRAINT users_security_dashboard_projects_pkey PRIMARY KEY USING INDEX users_security_dashboard_projects_unique_index")
-> 0.0009s
== 20201014163633 SpecifyPrimaryKeyWhereMissing: migrated (0.1108s) ===========
Migration down
Migration down log
== 20201014163633 SpecifyPrimaryKeyWhereMissing: reverting ====================
-- transaction_open?()
-> 0.0000s
-- index_exists?(:project_authorizations, [:user_id, :project_id, :access_level], {:name=>:index_project_authorizations_on_user_id_project_id_access_level, :unique=>true, :algorithm=>:concurrently})
-> 0.0024s
-- execute("SET statement_timeout TO 0")
-> 0.0002s
-- add_index(:project_authorizations, [:user_id, :project_id, :access_level], {:name=>:index_project_authorizations_on_user_id_project_id_access_level, :unique=>true, :algorithm=>:concurrently})
-> 0.0517s
-- execute("RESET ALL")
-> 0.0002s
-- execute("ALTER TABLE project_authorizations DROP CONSTRAINT project_authorizations_pkey")
-> 0.0009s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:analytics_language_trend_repository_languages, [:programming_language_id, :project_id, :snapshot_date], {:name=>:analytics_repository_languages_unique_index, :unique=>true, :algorithm=>:concurrently})
-> 0.0038s
-- add_index(:analytics_language_trend_repository_languages, [:programming_language_id, :project_id, :snapshot_date], {:name=>:analytics_repository_languages_unique_index, :unique=>true, :algorithm=>:concurrently})
-> 0.0181s
-- execute("ALTER TABLE analytics_language_trend_repository_languages DROP CONSTRAINT analytics_language_trend_repository_languages_pkey")
-> 0.0011s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:approval_project_rules_protected_branches, [:approval_project_rule_id, :protected_branch_id], {:name=>:index_approval_project_rules_protected_branches_unique, :unique=>true, :algorithm=>:concurrently})
-> 0.0016s
-- add_index(:approval_project_rules_protected_branches, [:approval_project_rule_id, :protected_branch_id], {:name=>:index_approval_project_rules_protected_branches_unique, :unique=>true, :algorithm=>:concurrently})
-> 0.0118s
-- execute("ALTER TABLE approval_project_rules_protected_branches DROP CONSTRAINT approval_project_rules_protected_branches_pkey")
-> 0.0011s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:ci_build_trace_sections, [:build_id, :section_name_id], {:name=>:index_ci_build_trace_sections_on_build_id_and_section_name_id, :unique=>true, :algorithm=>:concurrently})
-> 0.0023s
-- add_index(:ci_build_trace_sections, [:build_id, :section_name_id], {:name=>:index_ci_build_trace_sections_on_build_id_and_section_name_id, :unique=>true, :algorithm=>:concurrently})
-> 0.0117s
-- execute("ALTER TABLE ci_build_trace_sections DROP CONSTRAINT ci_build_trace_sections_pkey")
-> 0.0007s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:deployment_merge_requests, [:deployment_id, :merge_request_id], {:name=>:idx_deployment_merge_requests_unique_index, :unique=>true, :algorithm=>:concurrently})
-> 0.0031s
-- add_index(:deployment_merge_requests, [:deployment_id, :merge_request_id], {:name=>:idx_deployment_merge_requests_unique_index, :unique=>true, :algorithm=>:concurrently})
-> 0.0129s
-- execute("ALTER TABLE deployment_merge_requests DROP CONSTRAINT deployment_merge_requests_pkey")
-> 0.0006s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:issue_assignees, [:issue_id, :user_id], {:name=>:index_issue_assignees_on_issue_id_and_user_id, :unique=>true, :algorithm=>:concurrently})
-> 0.0015s
-- add_index(:issue_assignees, [:issue_id, :user_id], {:name=>:index_issue_assignees_on_issue_id_and_user_id, :unique=>true, :algorithm=>:concurrently})
-> 0.0128s
-- execute("ALTER TABLE issue_assignees DROP CONSTRAINT issue_assignees_pkey")
-> 0.0010s
-- transaction_open?()
-> 0.0000s
-- add_index(:issues_prometheus_alert_events, [:issue_id, :prometheus_alert_event_id], {:name=>:issue_id_prometheus_alert_event_id_index, :unique=>true, :algorithm=>:concurrently})
-> 0.0229s
-- execute("ALTER TABLE issues_prometheus_alert_events DROP CONSTRAINT issues_prometheus_alert_events_pkey")
-> 0.0009s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:issues_self_managed_prometheus_alert_events, [:issue_id, :self_managed_prometheus_alert_event_id], {:name=>:issue_id_self_managed_prometheus_alert_event_id_index, :unique=>true, :algorithm=>:concurrently})
-> 0.0028s
-- add_index(:issues_self_managed_prometheus_alert_events, [:issue_id, :self_managed_prometheus_alert_event_id], {:name=>:issue_id_self_managed_prometheus_alert_event_id_index, :unique=>true, :algorithm=>:concurrently})
-> 0.0118s
-- execute("ALTER TABLE issues_self_managed_prometheus_alert_events DROP CONSTRAINT issues_self_managed_prometheus_alert_events_pkey")
-> 0.0009s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:merge_request_diff_commits, [:merge_request_diff_id, :relative_order], {:name=>:index_merge_request_diff_commits_on_mr_diff_id_and_order, :unique=>true, :algorithm=>:concurrently})
-> 0.0017s
-- add_index(:merge_request_diff_commits, [:merge_request_diff_id, :relative_order], {:name=>:index_merge_request_diff_commits_on_mr_diff_id_and_order, :unique=>true, :algorithm=>:concurrently})
-> 0.0063s
-- execute("ALTER TABLE merge_request_diff_commits DROP CONSTRAINT merge_request_diff_commits_pkey")
-> 0.0004s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:merge_request_diff_files, [:merge_request_diff_id, :relative_order], {:name=>:index_merge_request_diff_files_on_mr_diff_id_and_order, :unique=>true, :algorithm=>:concurrently})
-> 0.0007s
-- add_index(:merge_request_diff_files, [:merge_request_diff_id, :relative_order], {:name=>:index_merge_request_diff_files_on_mr_diff_id_and_order, :unique=>true, :algorithm=>:concurrently})
-> 0.0075s
-- execute("ALTER TABLE merge_request_diff_files DROP CONSTRAINT merge_request_diff_files_pkey")
-> 0.0008s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:milestone_releases, [:milestone_id, :release_id], {:name=>:index_miletone_releases_on_milestone_and_release, :unique=>true, :algorithm=>:concurrently})
-> 0.0018s
-- add_index(:milestone_releases, [:milestone_id, :release_id], {:name=>:index_miletone_releases_on_milestone_and_release, :unique=>true, :algorithm=>:concurrently})
-> 0.0070s
-- execute("ALTER TABLE milestone_releases DROP CONSTRAINT milestone_releases_pkey")
-> 0.0004s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:project_pages_metadata, [:project_id], {:name=>:index_project_pages_metadata_on_project_id, :unique=>true, :algorithm=>:concurrently})
-> 0.0022s
-- add_index(:project_pages_metadata, [:project_id], {:name=>:index_project_pages_metadata_on_project_id, :unique=>true, :algorithm=>:concurrently})
-> 0.0059s
-- execute("ALTER TABLE project_pages_metadata DROP CONSTRAINT project_pages_metadata_pkey")
-> 0.0006s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:push_event_payloads, [:event_id], {:name=>:index_push_event_payloads_on_event_id, :unique=>true, :algorithm=>:concurrently})
-> 0.0027s
-- add_index(:push_event_payloads, [:event_id], {:name=>:index_push_event_payloads_on_event_id, :unique=>true, :algorithm=>:concurrently})
-> 0.0171s
-- execute("ALTER TABLE push_event_payloads DROP CONSTRAINT push_event_payloads_pkey")
-> 0.0012s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:repository_languages, [:project_id, :programming_language_id], {:name=>:index_repository_languages_on_project_and_languages_id, :unique=>true, :algorithm=>:concurrently})
-> 0.0012s
-- add_index(:repository_languages, [:project_id, :programming_language_id], {:name=>:index_repository_languages_on_project_and_languages_id, :unique=>true, :algorithm=>:concurrently})
-> 0.0072s
-- execute("ALTER TABLE repository_languages DROP CONSTRAINT repository_languages_pkey")
-> 0.0006s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:user_interacted_projects, [:project_id, :user_id], {:name=>:index_user_interacted_projects_on_project_id_and_user_id, :unique=>true, :algorithm=>:concurrently})
-> 0.0013s
-- add_index(:user_interacted_projects, [:project_id, :user_id], {:name=>:index_user_interacted_projects_on_project_id_and_user_id, :unique=>true, :algorithm=>:concurrently})
-> 0.0068s
-- execute("ALTER TABLE user_interacted_projects DROP CONSTRAINT user_interacted_projects_pkey")
-> 0.0008s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:users_security_dashboard_projects, [:project_id, :user_id], {:name=>:users_security_dashboard_projects_unique_index, :unique=>true, :algorithm=>:concurrently})
-> 0.0020s
-- add_index(:users_security_dashboard_projects, [:project_id, :user_id], {:name=>:users_security_dashboard_projects_unique_index, :unique=>true, :algorithm=>:concurrently})
-> 0.0085s
-- execute("ALTER TABLE users_security_dashboard_projects DROP CONSTRAINT users_security_dashboard_projects_pkey")
-> 0.0005s
== 20201014163633 SpecifyPrimaryKeyWhereMissing: reverted (0.4195s) ===========
</details>
</p>
Does this MR meet the acceptance criteria?
Conformity