Adds unique index and model validation on the member_approval table
What does this MR do and why?
Added new unique index
- On member_id, member_namespace_id, new_access_level to ensure only one record per member per namespace for the accesslevel exists in pending state
ref: #433172 (closed)
MR 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.
Database
up
main: == [advisory_lock_connection] object_id: 118240, pg_backend_pid: 10078
main: == 20240207193743 AddConditionalUniqueIndexToMemberApprovals: migrating =======
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0182s
main: -- index_exists?(:member_approvals, [:member_id, :member_namespace_id, :new_access_level], {:unique=>true, :where=>"status = 0", :name=>"index_member_approvals_on_unique_pending", :algorithm=>:concurrently})
main: -> 0.0030s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0002s
main: -- add_index(:member_approvals, [:member_id, :member_namespace_id, :new_access_level], {:unique=>true, :where=>"status = 0", :name=>"index_member_approvals_on_unique_pending", :algorithm=>:concurrently})
main: -> 0.0028s
main: -- execute("RESET statement_timeout")
main: -> 0.0002s
main: == 20240207193743 AddConditionalUniqueIndexToMemberApprovals: migrated (0.0386s)
main: == [advisory_lock_connection] object_id: 118240, pg_backend_pid: 10078
ci: == [advisory_lock_connection] object_id: 118480, pg_backend_pid: 10080
ci: == 20240207193743 AddConditionalUniqueIndexToMemberApprovals: migrating =======
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0005s
ci: -- index_exists?(:member_approvals, [:member_id, :member_namespace_id, :new_access_level], {:unique=>true, :where=>"status = 0", :name=>"index_member_approvals_on_unique_pending", :algorithm=>:concurrently})
ci: -> 0.0025s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0001s
ci: -- add_index(:member_approvals, [:member_id, :member_namespace_id, :new_access_level], {:unique=>true, :where=>"status = 0", :name=>"index_member_approvals_on_unique_pending", :algorithm=>:concurrently})
ci: -> 0.0021s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0003s
ci: == 20240207193743 AddConditionalUniqueIndexToMemberApprovals: migrated (0.0205s)
down
main: == [advisory_lock_connection] object_id: 117740, pg_backend_pid: 10532
main: == 20240207193743 AddConditionalUniqueIndexToMemberApprovals: reverting =======
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0185s
main: -- indexes(:member_approvals)
main: -> 0.0028s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0001s
main: -- remove_index(:member_approvals, {:algorithm=>:concurrently, :name=>"index_member_approvals_on_unique_pending"})
main: -> 0.0016s
main: -- execute("RESET statement_timeout")
main: -> 0.0002s
main: == 20240207193743 AddConditionalUniqueIndexToMemberApprovals: reverted (0.0352s)
main: == [advisory_lock_connection] object_id: 117740, pg_backend_pid: 10532
ci: == [advisory_lock_connection] object_id: 117740, pg_backend_pid: 10970
ci: == 20240207193743 AddConditionalUniqueIndexToMemberApprovals: reverting =======
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0184s
ci: -- indexes(:member_approvals)
ci: -> 0.0037s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0002s
ci: -- remove_index(:member_approvals, {:algorithm=>:concurrently, :name=>"index_member_approvals_on_unique_pending"})
ci: -> 0.0017s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0002s
ci: == 20240207193743 AddConditionalUniqueIndexToMemberApprovals: reverted (0.0418s)
ci: == [advisory_lock_connection] object_id: 117740, pg_backend_pid: 10970