Skip to content

Add not null contraint for board recent visits columns

Jarka Košanová requested to merge 330089-null-check into master

What does this MR do and why?

Id adds not null constraints to the following tables and columns:

  • board_group_recent_visits: user_id, group_id, board_id
  • board_project_recent_visits: user_id, project_id, board_id

The post-deployment migrations include the verification as we don't have any records with null values: comment, explain 1, explain 2.

We also already have the validation on the models: https://gitlab.com/gitlab-org/gitlab/-/blob/master/app/models/board_group_recent_visit.rb#L11 and https://gitlab.com/gitlab-org/gitlab/-/blob/master/app/models/board_project_recent_visit.rb#L11.

Migration output

Up 
main: == 20220901184106 AddNotNullToBoardGroupRecentVisits: migrating ===============
main: -- current_schema()
main:    -> 0.0024s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- current_schema()
main:    -> 0.0007s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE board_group_recent_visits\nADD CONSTRAINT check_409f6caea4\nCHECK ( user_id IS NOT NULL )\nNOT VALID;\n")
main:    -> 0.0014s
main: -- current_schema()
main:    -> 0.0006s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- current_schema()
main:    -> 0.0004s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE board_group_recent_visits\nADD CONSTRAINT check_ddc74243ef\nCHECK ( group_id IS NOT NULL )\nNOT VALID;\n")
main:    -> 0.0012s
main: -- current_schema()
main:    -> 0.0005s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- current_schema()
main:    -> 0.0005s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE board_group_recent_visits\nADD CONSTRAINT check_fa7711a898\nCHECK ( board_id IS NOT NULL )\nNOT VALID;\n")
main:    -> 0.0012s
main: == 20220901184106 AddNotNullToBoardGroupRecentVisits: migrated (0.0498s) ======

main: == 20220901184246 AddNotNullToBoardProjectRecentVisits: migrating =============
main: -- current_schema()
main:    -> 0.0005s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- current_schema()
main:    -> 0.0006s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE board_project_recent_visits\nADD CONSTRAINT check_df7762a99a\nCHECK ( user_id IS NOT NULL )\nNOT VALID;\n")
main:    -> 0.0011s
main: -- current_schema()
main:    -> 0.0005s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- current_schema()
main:    -> 0.0004s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE board_project_recent_visits\nADD CONSTRAINT check_d9cc9b79da\nCHECK ( project_id IS NOT NULL )\nNOT VALID;\n")
main:    -> 0.0015s
main: -- current_schema()
main:    -> 0.0004s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- current_schema()
main:    -> 0.0005s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE board_project_recent_visits\nADD CONSTRAINT check_0386e26981\nCHECK ( board_id IS NOT NULL )\nNOT VALID;\n")
main:    -> 0.0011s
main: == 20220901184246 AddNotNullToBoardProjectRecentVisits: migrated (0.0329s) ====

main: == 20220902111016 DeleteNullRecordsFromBoardGroupRecentVisits: migrating ======
main: -- execute("DELETE FROM board_group_recent_visits WHERE user_id is null OR group_id is null OR board_id is null")
main:    -> 0.0026s
main: == 20220902111016 DeleteNullRecordsFromBoardGroupRecentVisits: migrated (0.0027s)

main: == 20220902111038 DeleteNullRecordsFromBoardProjectRecentVisits: migrating ====
main: -- execute("DELETE FROM board_project_recent_visits WHERE user_id is null OR project_id is null OR board_id is null")
main:    -> 0.0011s
main: == 20220902111038 DeleteNullRecordsFromBoardProjectRecentVisits: migrated (0.0012s)

main: == 20220904173342 ValidateNotNullConstraintBoardGroupRecentVisits: migrating ==
main: -- current_schema()
main:    -> 0.0005s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0005s
main: -- execute("ALTER TABLE board_group_recent_visits VALIDATE CONSTRAINT check_409f6caea4;")
main:    -> 0.0010s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: -- current_schema()
main:    -> 0.0004s
main: -- execute("ALTER TABLE board_group_recent_visits VALIDATE CONSTRAINT check_ddc74243ef;")
main:    -> 0.0013s
main: -- current_schema()
main:    -> 0.0007s
main: -- execute("ALTER TABLE board_group_recent_visits VALIDATE CONSTRAINT check_fa7711a898;")
main:    -> 0.0006s
main: == 20220904173342 ValidateNotNullConstraintBoardGroupRecentVisits: migrated (0.0142s)

main: == 20220904173430 ValidateNotNullConstraintBoardProjectRecentVisits: migrating
main: -- current_schema()
main:    -> 0.0005s
main: -- execute("ALTER TABLE board_project_recent_visits VALIDATE CONSTRAINT check_df7762a99a;")
main:    -> 0.0007s
main: -- current_schema()
main:    -> 0.0004s
main: -- execute("ALTER TABLE board_project_recent_visits VALIDATE CONSTRAINT check_d9cc9b79da;")
main:    -> 0.0006s
main: -- current_schema()
main:    -> 0.0003s
main: -- execute("ALTER TABLE board_project_recent_visits VALIDATE CONSTRAINT check_0386e26981;")
main:    -> 0.0014s
main: == 20220904173430 ValidateNotNullConstraintBoardProjectRecentVisits: migrated (0.0104s)
Down 
main: == 20220904173430 ValidateNotNullConstraintBoardProjectRecentVisits: reverting
main: == 20220904173430 ValidateNotNullConstraintBoardProjectRecentVisits: reverted (0.0010s)

main: == 20220904173342 ValidateNotNullConstraintBoardGroupRecentVisits: reverting ==
main: == 20220904173342 ValidateNotNullConstraintBoardGroupRecentVisits: reverted (0.0001s)

main: == 20220902111038 DeleteNullRecordsFromBoardProjectRecentVisits: reverting ====
main: == 20220902111038 DeleteNullRecordsFromBoardProjectRecentVisits: reverted (0.0001s)

main: == 20220902111016 DeleteNullRecordsFromBoardGroupRecentVisits: reverting ======
main: == 20220902111016 DeleteNullRecordsFromBoardGroupRecentVisits: reverted (0.0001s)

main: == 20220901184246 AddNotNullToBoardProjectRecentVisits: reverting =============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE board_project_recent_visits\nDROP CONSTRAINT IF EXISTS check_df7762a99a\n")
main:    -> 0.0012s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE board_project_recent_visits\nDROP CONSTRAINT IF EXISTS check_d9cc9b79da\n")
main:    -> 0.0008s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE board_project_recent_visits\nDROP CONSTRAINT IF EXISTS check_0386e26981\n")
main:    -> 0.0006s
main: == 20220901184246 AddNotNullToBoardProjectRecentVisits: reverted (0.0167s) ====

main: == 20220901184106 AddNotNullToBoardGroupRecentVisits: reverting ===============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE board_group_recent_visits\nDROP CONSTRAINT IF EXISTS check_409f6caea4\n")
main:    -> 0.0008s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE board_group_recent_visits\nDROP CONSTRAINT IF EXISTS check_fa7711a898\n")
main:    -> 0.0006s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE board_group_recent_visits\nDROP CONSTRAINT IF EXISTS check_ddc74243ef\n")
main:    -> 0.0006s
main: == 20220901184106 AddNotNullToBoardGroupRecentVisits: reverted (0.0103s) ======

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #330089 (closed)

Edited by Jarka Košanová

Merge request reports