Skip to content

Adds unique index and model validation on the member_approval table

Suraj Tripathi requested to merge 433172_add_db_constrint_for_member_approval into master

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

Merge request reports