Skip to content

Add migration for adding rule_type to approval_project_rules

Lucas Charles requested to merge 9928ee-add-rule_type-to-approval-project-rules into master

What does this MR do?

With the work in gitlab-ee#9928 we are adding an additional rule_type to approval_merge_request_rules.

Per discussion in https://gitlab.com/gitlab-org/gitlab-ee/merge_requests/13109#note_188457022 we should add an additional column to provide parity between the introduced approvals_project_rule and the approvals_merge_request_rules. These migrations are needed in order to support that work.

Prior to introducing a secondary rule_type https://gitlab.com/gitlab-org/gitlab-ee/merge_requests/13109 this index will primarily be used when rendering visible approvals on the project settings page

EXPLAIN for gitlab.com affected rows when setting default (37885 rows)

Aggregate  (cost=1204.68..1204.69 rows=1 width=8) (actual time=37.794..37.794 rows=1 loops=1)
  Buffers: shared hit=13286 read=42
  I/O Timings: read=11.964
  ->  Index Only Scan using index_approval_project_rules_on_project_id on approval_project_rules  (cost=0.29..1109.96 rows=37885 width=0) (actual time=0.153..34.213 rows=37741 loops=1)
        Heap Fetches: 14400
        Buffers: shared hit=13286 read=42
        I/O Timings: read=11.964
Planning time: 0.817 ms
Execution time: 37.872 ms

Migration

== 20190709204413 AddRuleTypeToApprovalProjectRules: migrating ================
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- transaction()
-- add_column(:approval_project_rules, :rule_type, :integer, {:default=>nil})
   -> 0.0007s
-- change_column_default(:approval_project_rules, :rule_type, 0)
   -> 0.0015s
   -> 0.0034s
-- columns(:approval_project_rules)
   -> 0.0036s
-- transaction_open?()
   -> 0.0000s
-- exec_query("SELECT COUNT(*) AS count FROM \"approval_project_rules\"")
   -> 0.0041s
-- change_column_null(:approval_project_rules, :rule_type, false)
   -> 0.0077s
-- execute("RESET ALL")
   -> 0.0002s
== 20190709204413 AddRuleTypeToApprovalProjectRules: migrated (0.0206s) =======

== 20190710151229 AddIndexToApprovalProjectRulesRuleType: migrating ===========
-- transaction_open?()
   -> 0.0000s
-- select_one("SELECT current_setting('server_version_num') AS v")
   -> 0.0006s
-- index_exists?(:approval_project_rules, :rule_type, {:algorithm=>:concurrently})
   -> 0.0013s
== 20190710151229 AddIndexToApprovalProjectRulesRuleType: migrated (0.0020s) ==

Database checklist

When adding migrations:

  • Updated db/schema.rb
  • Added a down method so the migration can be reverted
  • Added the output of the migration(s) to the MR body
  • [-] Added tests for the migration in spec/migrations if necessary (e.g. when migrating data)

When adding or modifying queries to improve performance:

  • Included data that shows the performance improvement, preferably in the form of a benchmark
  • Included the output of EXPLAIN (ANALYZE, BUFFERS) of the relevant queries

When adding foreign keys to existing tables:

  • [-] Included a migration to remove orphaned rows in the source table before adding the foreign key
  • [-] Removed any instances of dependent: ... that may no longer be necessary

When adding tables:

  • [-] Ordered columns based on the Ordering Table Columns guidelines
  • [-] Added foreign keys to any columns pointing to data in other tables
  • [-] Added indexes for fields that are used in statements such as WHERE, ORDER BY, GROUP BY, and JOINs

When removing columns, tables, indexes or other structures:

  • [-] Removed these in a post-deployment migration
  • [-] Made sure the application no longer uses (or ignores) these structures

General checklist

Edited by Mayra Cabrera

Merge request reports