Add milestones not null sharding key constraint
What does this MR do and why?
Preventive cleanup of invalid records from milestones table even though we don't have any offending records in .com
Adding a not null constraint to sharding key after the cleanup.
Query plans
No offending records in .com, cleanup migrations introduced just in case self hosted has some offending records as before we didn't have a DB level constraint. So plans won't hit any records in .com, but the explain shows that we are using the index and no rows are affected
DELETE where both missing
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/35740/commands/110493
delete from milestones where group_id IS NULL AND project_id IS NULLRemove group_id where both present
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/35740/commands/110496
UPDATE milestones SET group_id = NULL WHERE (group_id IS NOT NULL AND project_id IS NOT NULL)Migration output
UP
main: == [advisory_lock_connection] object_id: 132680, pg_backend_pid: 48438
main: == 20250131003148 IndexMilestonesProjectAndGroupBothPresent: migrating ========
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0115s
main: -- index_exists?(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_present", :where=>"group_id IS NOT NULL AND project_id IS NOT NULL", :algorithm=>:concurrently})
main: -> 0.0055s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0003s
main: -- add_index(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_present", :where=>"group_id IS NOT NULL AND project_id IS NOT NULL", :algorithm=>:concurrently})
main: -> 0.0057s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: == 20250131003148 IndexMilestonesProjectAndGroupBothPresent: migrated (0.0480s)
main: == [advisory_lock_connection] object_id: 132680, pg_backend_pid: 48438
ci: == [advisory_lock_connection] object_id: 132920, pg_backend_pid: 48440
ci: == 20250131003148 IndexMilestonesProjectAndGroupBothPresent: migrating ========
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0014s
ci: -- index_exists?(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_present", :where=>"group_id IS NOT NULL AND project_id IS NOT NULL", :algorithm=>:concurrently})
ci: -> 0.0064s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0003s
ci: -- add_index(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_present", :where=>"group_id IS NOT NULL AND project_id IS NOT NULL", :algorithm=>:concurrently})
ci: -> 0.0054s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0005s
ci: == 20250131003148 IndexMilestonesProjectAndGroupBothPresent: migrated (0.0442s)
ci: == [advisory_lock_connection] object_id: 132920, pg_backend_pid: 48440
main: == [advisory_lock_connection] object_id: 133140, pg_backend_pid: 48444
main: == 20250131003257 IndexMilestonesProjectAndGroupBothMissing: migrating ========
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0004s
main: -- index_exists?(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_missing", :where=>"group_id IS NULL AND project_id IS NULL", :algorithm=>:concurrently})
main: -> 0.0027s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0003s
main: -- add_index(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_missing", :where=>"group_id IS NULL AND project_id IS NULL", :algorithm=>:concurrently})
main: -> 0.0025s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: == 20250131003257 IndexMilestonesProjectAndGroupBothMissing: migrated (0.0137s)
main: == [advisory_lock_connection] object_id: 133140, pg_backend_pid: 48444
ci: == [advisory_lock_connection] object_id: 146520, pg_backend_pid: 48446
ci: == 20250131003257 IndexMilestonesProjectAndGroupBothMissing: migrating ========
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0007s
ci: -- index_exists?(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_missing", :where=>"group_id IS NULL AND project_id IS NULL", :algorithm=>:concurrently})
ci: -> 0.0034s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0003s
ci: -- add_index(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_missing", :where=>"group_id IS NULL AND project_id IS NULL", :algorithm=>:concurrently})
ci: -> 0.0023s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0006s
ci: == 20250131003257 IndexMilestonesProjectAndGroupBothMissing: migrated (0.0196s)
ci: == [advisory_lock_connection] object_id: 146520, pg_backend_pid: 48446
main: == [advisory_lock_connection] object_id: 157660, pg_backend_pid: 48449
main: == 20250131004135 DeleteMilestonesWithoutProjectOrGroup: migrating ============
main: -- transaction_open?(nil)
main: -> 0.0000s
main: == 20250131004135 DeleteMilestonesWithoutProjectOrGroup: migrated (0.0235s) ===
main: == [advisory_lock_connection] object_id: 157660, pg_backend_pid: 48449
ci: == [advisory_lock_connection] object_id: 169280, pg_backend_pid: 48451
ci: == 20250131004135 DeleteMilestonesWithoutProjectOrGroup: migrating ============
ci: -- The migration is skipped since it modifies the schemas: [:gitlab_main].
ci: -- This database can only apply migrations in one of the following schemas: [:gitlab_ci, :gitlab_internal, :gitlab_shared].
ci: == 20250131004135 DeleteMilestonesWithoutProjectOrGroup: migrated (0.0175s) ===
ci: == [advisory_lock_connection] object_id: 169280, pg_backend_pid: 48451
main: == [advisory_lock_connection] object_id: 181060, pg_backend_pid: 48454
main: == 20250131004257 FixMilestonesWithWithProjectAndGroup: migrating =============
main: -- transaction_open?(nil)
main: -> 0.0000s
main: == 20250131004257 FixMilestonesWithWithProjectAndGroup: migrated (0.0082s) ====
main: == [advisory_lock_connection] object_id: 181060, pg_backend_pid: 48454
ci: == [advisory_lock_connection] object_id: 187900, pg_backend_pid: 48456
ci: == 20250131004257 FixMilestonesWithWithProjectAndGroup: migrating =============
ci: -- The migration is skipped since it modifies the schemas: [:gitlab_main].
ci: -- This database can only apply migrations in one of the following schemas: [:gitlab_ci, :gitlab_internal, :gitlab_shared].
ci: == 20250131004257 FixMilestonesWithWithProjectAndGroup: migrated (0.0077s) ====
ci: == [advisory_lock_connection] object_id: 187900, pg_backend_pid: 48456
main: == [advisory_lock_connection] object_id: 188180, pg_backend_pid: 48459
main: == 20250131022137 AddMilestonesUniqueParentConstraint: migrating ==============
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- execute("ALTER TABLE milestones\nADD CONSTRAINT check_08e9c27987\nCHECK ( num_nonnulls(group_id, project_id) = 1 )\nNOT VALID;\n")
main: -> 0.0023s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0003s
main: -- execute("ALTER TABLE milestones VALIDATE CONSTRAINT check_08e9c27987;")
main: -> 0.0005s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: == 20250131022137 AddMilestonesUniqueParentConstraint: migrated (0.0280s) =====
main: == [advisory_lock_connection] object_id: 188180, pg_backend_pid: 48459
ci: == [advisory_lock_connection] object_id: 195080, pg_backend_pid: 48461
ci: == 20250131022137 AddMilestonesUniqueParentConstraint: migrating ==============
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- execute("ALTER TABLE milestones\nADD CONSTRAINT check_08e9c27987\nCHECK ( num_nonnulls(group_id, project_id) = 1 )\nNOT VALID;\n")
ci: -> 0.0019s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0071s
ci: -- execute("ALTER TABLE milestones VALIDATE CONSTRAINT check_08e9c27987;")
ci: -> 0.0006s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0003s
ci: == 20250131022137 AddMilestonesUniqueParentConstraint: migrated (0.0319s) =====
ci: == [advisory_lock_connection] object_id: 195080, pg_backend_pid: 48461
main: == [advisory_lock_connection] object_id: 195400, pg_backend_pid: 48464
main: == 20250131022504 DropMilestonesTempParentIndexBothMissing: migrating =========
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0006s
main: -- index_exists?(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_missing", :algorithm=>:concurrently})
main: -> 0.0069s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0003s
main: -- remove_index(:milestones, {:name=>"tmp_idx_milestones_on_project_group_both_missing", :algorithm=>:concurrently, :column=>:id})
main: -> 0.0068s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: == 20250131022504 DropMilestonesTempParentIndexBothMissing: migrated (0.0375s)
main: == [advisory_lock_connection] object_id: 195400, pg_backend_pid: 48464
ci: == [advisory_lock_connection] object_id: 202960, pg_backend_pid: 48466
ci: == 20250131022504 DropMilestonesTempParentIndexBothMissing: migrating =========
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0007s
ci: -- index_exists?(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_missing", :algorithm=>:concurrently})
ci: -> 0.0071s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0003s
ci: -- remove_index(:milestones, {:name=>"tmp_idx_milestones_on_project_group_both_missing", :algorithm=>:concurrently, :column=>:id})
ci: -> 0.0076s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0004s
ci: == 20250131022504 DropMilestonesTempParentIndexBothMissing: migrated (0.0464s)
ci: == [advisory_lock_connection] object_id: 202960, pg_backend_pid: 48466
main: == [advisory_lock_connection] object_id: 203620, pg_backend_pid: 48469
main: == 20250131022627 DropMilestonesTempParentIndexBothPresent: migrating =========
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0005s
main: -- index_exists?(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_present", :algorithm=>:concurrently})
main: -> 0.0049s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0003s
main: -- remove_index(:milestones, {:name=>"tmp_idx_milestones_on_project_group_both_present", :algorithm=>:concurrently, :column=>:id})
main: -> 0.0055s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: == 20250131022627 DropMilestonesTempParentIndexBothPresent: migrated (0.0318s)
main: == [advisory_lock_connection] object_id: 203620, pg_backend_pid: 48469
ci: == [advisory_lock_connection] object_id: 224140, pg_backend_pid: 48471
ci: == 20250131022627 DropMilestonesTempParentIndexBothPresent: migrating =========
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0009s
ci: -- index_exists?(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_present", :algorithm=>:concurrently})
ci: -> 0.0033s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0003s
ci: -- remove_index(:milestones, {:name=>"tmp_idx_milestones_on_project_group_both_present", :algorithm=>:concurrently, :column=>:id})
ci: -> 0.0047s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0003s
ci: == 20250131022627 DropMilestonesTempParentIndexBothPresent: migrated (0.0265s)
ci: == [advisory_lock_connection] object_id: 224140, pg_backend_pid: 48471DOWN
main: == [advisory_lock_connection] object_id: 132260, pg_backend_pid: 49058
main: == 20250131022627 DropMilestonesTempParentIndexBothPresent: reverting =========
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0119s
main: -- index_exists?(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_present", :where=>"group_id IS NOT NULL AND project_id IS NOT NULL", :algorithm=>:concurrently})
main: -> 0.0026s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0003s
main: -- add_index(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_present", :where=>"group_id IS NOT NULL AND project_id IS NOT NULL", :algorithm=>:concurrently})
main: -> 0.0016s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: == 20250131022627 DropMilestonesTempParentIndexBothPresent: reverted (0.0290s)
main: == 20250131022504 DropMilestonesTempParentIndexBothMissing: reverting =========
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0003s
main: -- index_exists?(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_missing", :where=>"group_id IS NULL AND project_id IS NULL", :algorithm=>:concurrently})
main: -> 0.0019s
main: -- add_index(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_missing", :where=>"group_id IS NULL AND project_id IS NULL", :algorithm=>:concurrently})
main: -> 0.0009s
main: == 20250131022504 DropMilestonesTempParentIndexBothMissing: reverted (0.0095s)
main: == 20250131022137 AddMilestonesUniqueParentConstraint: reverting ==============
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- execute(" ALTER TABLE milestones\n DROP CONSTRAINT IF EXISTS check_08e9c27987\n")
main: -> 0.0007s
main: == 20250131022137 AddMilestonesUniqueParentConstraint: reverted (0.0297s) =====
main: == 20250131004257 FixMilestonesWithWithProjectAndGroup: reverting =============
main: == 20250131004257 FixMilestonesWithWithProjectAndGroup: reverted (0.0027s) ====
main: == 20250131004135 DeleteMilestonesWithoutProjectOrGroup: reverting ============
main: == 20250131004135 DeleteMilestonesWithoutProjectOrGroup: reverted (0.0026s) ===
main: == 20250131003257 IndexMilestonesProjectAndGroupBothMissing: reverting ========
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0004s
main: -- index_exists?(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_missing", :algorithm=>:concurrently})
main: -> 0.0027s
main: -- remove_index(:milestones, {:name=>"tmp_idx_milestones_on_project_group_both_missing", :algorithm=>:concurrently, :column=>:id})
main: -> 0.0030s
main: == 20250131003257 IndexMilestonesProjectAndGroupBothMissing: reverted (0.0126s)
main: == 20250131003148 IndexMilestonesProjectAndGroupBothPresent: reverting ========
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0003s
main: -- index_exists?(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_present", :algorithm=>:concurrently})
main: -> 0.0019s
main: -- remove_index(:milestones, {:name=>"tmp_idx_milestones_on_project_group_both_present", :algorithm=>:concurrently, :column=>:id})
main: -> 0.0028s
main: == 20250131003148 IndexMilestonesProjectAndGroupBothPresent: reverted (0.0112s)
main: == [advisory_lock_connection] object_id: 132260, pg_backend_pid: 49058
ci: == [advisory_lock_connection] object_id: 194960, pg_backend_pid: 49309
ci: == 20250131022627 DropMilestonesTempParentIndexBothPresent: reverting =========
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0007s
ci: -- index_exists?(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_present", :where=>"group_id IS NOT NULL AND project_id IS NOT NULL", :algorithm=>:concurrently})
ci: -> 0.0057s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0004s
ci: -- add_index(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_present", :where=>"group_id IS NOT NULL AND project_id IS NOT NULL", :algorithm=>:concurrently})
ci: -> 0.0029s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0003s
ci: == 20250131022627 DropMilestonesTempParentIndexBothPresent: reverted (0.0343s)
ci: == 20250131022504 DropMilestonesTempParentIndexBothMissing: reverting =========
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0006s
ci: -- index_exists?(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_missing", :where=>"group_id IS NULL AND project_id IS NULL", :algorithm=>:concurrently})
ci: -> 0.0038s
ci: -- add_index(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_missing", :where=>"group_id IS NULL AND project_id IS NULL", :algorithm=>:concurrently})
ci: -> 0.0018s
ci: == 20250131022504 DropMilestonesTempParentIndexBothMissing: reverted (0.0308s)
ci: == 20250131022137 AddMilestonesUniqueParentConstraint: reverting ==============
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- execute(" ALTER TABLE milestones\n DROP CONSTRAINT IF EXISTS check_08e9c27987\n")
ci: -> 0.0008s
ci: == 20250131022137 AddMilestonesUniqueParentConstraint: reverted (0.0157s) =====
ci: == 20250131004257 FixMilestonesWithWithProjectAndGroup: reverting =============
ci: -- The migration is skipped since it modifies the schemas: [:gitlab_main].
ci: -- This database can only apply migrations in one of the following schemas: [:gitlab_ci, :gitlab_internal, :gitlab_shared].
ci: == 20250131004257 FixMilestonesWithWithProjectAndGroup: reverted (0.0130s) ====
ci: == 20250131004135 DeleteMilestonesWithoutProjectOrGroup: reverting ============
ci: -- The migration is skipped since it modifies the schemas: [:gitlab_main].
ci: -- This database can only apply migrations in one of the following schemas: [:gitlab_ci, :gitlab_internal, :gitlab_shared].
ci: == 20250131004135 DeleteMilestonesWithoutProjectOrGroup: reverted (0.0123s) ===
ci: == 20250131003257 IndexMilestonesProjectAndGroupBothMissing: reverting ========
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0007s
ci: -- index_exists?(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_missing", :algorithm=>:concurrently})
ci: -> 0.0041s
ci: -- remove_index(:milestones, {:name=>"tmp_idx_milestones_on_project_group_both_missing", :algorithm=>:concurrently, :column=>:id})
ci: -> 0.0051s
ci: == 20250131003257 IndexMilestonesProjectAndGroupBothMissing: reverted (0.0334s)
ci: == 20250131003148 IndexMilestonesProjectAndGroupBothPresent: reverting ========
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0006s
ci: -- index_exists?(:milestones, :id, {:name=>"tmp_idx_milestones_on_project_group_both_present", :algorithm=>:concurrently})
ci: -> 0.0030s
ci: -- remove_index(:milestones, {:name=>"tmp_idx_milestones_on_project_group_both_present", :algorithm=>:concurrently, :column=>:id})
ci: -> 0.0040s
ci: == 20250131003148 IndexMilestonesProjectAndGroupBothPresent: reverted (0.0280s)
ci: == [advisory_lock_connection] object_id: 194960, pg_backend_pid: 49309MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Related to #514593 (closed)
Edited by Mario Celi