Skip to content

Prevent adding new records with null attributes

Abdul Wadoodrequested to merge
388393-add-not-null into master

What does this MR do and why?

This MR is step 5 of making a column NOT NULL according to our database guidelines. This prevents adding new records with null attributes to the users and user_preferences tables.

Summary of changes

MR Step
Fill null values of users table with their defa... (!125881 - merged) 1
Check records and finalize the migrations to fi... (#423000 - closed) (TBD) 2 & 3
Validate the presence of default User and UserP... (!127587 - merged) 4
Prevent adding new records with null attributes (!125744 - merged) 5 👈 You are here

Migration out

up 
bin/rails db:migrate
main: == [advisory_lock_connection] object_id: 226100, pg_backend_pid: 33757
main: == 20230823085514 AddNotNullToUserPreferencesColumns: migrating ===============
main: -- current_schema()
main:    -> 0.0013s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE user_preferences\nADD CONSTRAINT check_d3248b1b9c\nCHECK ( tab_width IS NOT NULL )\nNOT VALID;\n")
main:    -> 0.0009s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- execute("ALTER TABLE user_preferences VALIDATE CONSTRAINT check_d3248b1b9c;")
main:    -> 0.0005s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: -- current_schema()
main:    -> 0.0002s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE user_preferences\nADD CONSTRAINT check_1d670edc68\nCHECK ( time_display_relative IS NOT NULL )\nNOT VALID;\n")
main:    -> 0.0006s
main: -- execute("ALTER TABLE user_preferences VALIDATE CONSTRAINT check_1d670edc68;")
main:    -> 0.0004s
main: -- current_schema()
main:    -> 0.0003s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE user_preferences\nADD CONSTRAINT check_b22446f91a\nCHECK ( render_whitespace_in_code IS NOT NULL )\nNOT VALID;\n")
main:    -> 0.0005s
main: -- execute("ALTER TABLE user_preferences VALIDATE CONSTRAINT check_b22446f91a;")
main:    -> 0.0005s
main: == 20230823085514 AddNotNullToUserPreferencesColumns: migrated (0.1555s) ======

main: == [advisory_lock_connection] object_id: 226100, pg_backend_pid: 33757
ci: == [advisory_lock_connection] object_id: 226420, pg_backend_pid: 33760
ci: == 20230823085514 AddNotNullToUserPreferencesColumns: migrating ===============
ci: -- current_schema()
ci:    -> 0.0003s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE user_preferences\nADD CONSTRAINT check_d3248b1b9c\nCHECK ( tab_width IS NOT NULL )\nNOT VALID;\n")
ci:    -> 0.0009s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE user_preferences VALIDATE CONSTRAINT check_d3248b1b9c;")
ci:    -> 0.0006s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: -- current_schema()
ci:    -> 0.0003s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE user_preferences\nADD CONSTRAINT check_1d670edc68\nCHECK ( time_display_relative IS NOT NULL )\nNOT VALID;\n")
ci:    -> 0.0005s
ci: -- execute("ALTER TABLE user_preferences VALIDATE CONSTRAINT check_1d670edc68;")
ci:    -> 0.0005s
ci: -- current_schema()
ci:    -> 0.0002s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE user_preferences\nADD CONSTRAINT check_b22446f91a\nCHECK ( render_whitespace_in_code IS NOT NULL )\nNOT VALID;\n")
ci:    -> 0.0004s
ci: -- execute("ALTER TABLE user_preferences VALIDATE CONSTRAINT check_b22446f91a;")
ci:    -> 0.0006s
ci: == 20230823085514 AddNotNullToUserPreferencesColumns: migrated (0.0377s) ======

ci: == [advisory_lock_connection] object_id: 226420, pg_backend_pid: 33760
main: == [advisory_lock_connection] object_id: 226700, pg_backend_pid: 33763
main: == 20230823085824 AddNotNullToUsersColumns: migrating =========================
main: -- current_schema()
main:    -> 0.0003s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE users\nADD CONSTRAINT check_693c6f3aab\nCHECK ( hide_no_ssh_key IS NOT NULL )\nNOT VALID;\n")
main:    -> 0.0010s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- execute("ALTER TABLE users VALIDATE CONSTRAINT check_693c6f3aab;")
main:    -> 0.0005s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: -- current_schema()
main:    -> 0.0003s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE users\nADD CONSTRAINT check_3a60c18afc\nCHECK ( hide_no_password IS NOT NULL )\nNOT VALID;\n")
main:    -> 0.0005s
main: -- execute("ALTER TABLE users VALIDATE CONSTRAINT check_3a60c18afc;")
main:    -> 0.0007s
main: -- current_schema()
main:    -> 0.0002s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE users\nADD CONSTRAINT check_061f6f1c91\nCHECK ( project_view IS NOT NULL )\nNOT VALID;\n")
main:    -> 0.0009s
main: -- execute("ALTER TABLE users VALIDATE CONSTRAINT check_061f6f1c91;")
main:    -> 0.0005s
main: -- current_schema()
main:    -> 0.0004s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0003s
main: -- execute("ALTER TABLE users\nADD CONSTRAINT check_c737c04b87\nCHECK ( notified_of_own_activity IS NOT NULL )\nNOT VALID;\n")
main:    -> 0.0007s
main: -- execute("ALTER TABLE users VALIDATE CONSTRAINT check_c737c04b87;")
main:    -> 0.0007s
main: == 20230823085824 AddNotNullToUsersColumns: migrated (0.0405s) ================

main: == [advisory_lock_connection] object_id: 226700, pg_backend_pid: 33763
ci: == [advisory_lock_connection] object_id: 226960, pg_backend_pid: 33766
ci: == 20230823085824 AddNotNullToUsersColumns: migrating =========================
ci: -- current_schema()
ci:    -> 0.0003s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE users\nADD CONSTRAINT check_693c6f3aab\nCHECK ( hide_no_ssh_key IS NOT NULL )\nNOT VALID;\n")
ci:    -> 0.0008s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE users VALIDATE CONSTRAINT check_693c6f3aab;")
ci:    -> 0.0005s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0004s
ci: -- current_schema()
ci:    -> 0.0002s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE users\nADD CONSTRAINT check_3a60c18afc\nCHECK ( hide_no_password IS NOT NULL )\nNOT VALID;\n")
ci:    -> 0.0006s
ci: -- execute("ALTER TABLE users VALIDATE CONSTRAINT check_3a60c18afc;")
ci:    -> 0.0006s
ci: -- current_schema()
ci:    -> 0.0002s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE users\nADD CONSTRAINT check_061f6f1c91\nCHECK ( project_view IS NOT NULL )\nNOT VALID;\n")
ci:    -> 0.0005s
ci: -- execute("ALTER TABLE users VALIDATE CONSTRAINT check_061f6f1c91;")
ci:    -> 0.0006s
ci: -- current_schema()
ci:    -> 0.0002s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE users\nADD CONSTRAINT check_c737c04b87\nCHECK ( notified_of_own_activity IS NOT NULL )\nNOT VALID;\n")
ci:    -> 0.0006s
ci: -- execute("ALTER TABLE users VALIDATE CONSTRAINT check_c737c04b87;")
ci:    -> 0.0006s
ci: == 20230823085824 AddNotNullToUsersColumns: migrated (0.0411s) ================

ci: == [advisory_lock_connection] object_id: 226960, pg_backend_pid: 33766
down 
bin/rails db:rollback:ci && bin/rails db:rollback:main && bin/rails db:rollback:ci && bin/rails db:rollback:main
ci: == [advisory_lock_connection] object_id: 224820, pg_backend_pid: 30989
ci: == 20230823085824 AddNotNullToUsersColumns: reverting =========================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("            ALTER TABLE users\n            DROP CONSTRAINT IF EXISTS check_693c6f3aab\n")
ci:    -> 0.0009s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("            ALTER TABLE users\n            DROP CONSTRAINT IF EXISTS check_3a60c18afc\n")
ci:    -> 0.0006s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("            ALTER TABLE users\n            DROP CONSTRAINT IF EXISTS check_061f6f1c91\n")
ci:    -> 0.0005s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("            ALTER TABLE users\n            DROP CONSTRAINT IF EXISTS check_c737c04b87\n")
ci:    -> 0.0004s
ci: == 20230823085824 AddNotNullToUsersColumns: reverted (0.0276s) ================

ci: == [advisory_lock_connection] object_id: 224820, pg_backend_pid: 30989
main: == [advisory_lock_connection] object_id: 224820, pg_backend_pid: 31542
main: == 20230823085824 AddNotNullToUsersColumns: reverting =========================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("            ALTER TABLE users\n            DROP CONSTRAINT IF EXISTS check_693c6f3aab\n")
main:    -> 0.0009s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("            ALTER TABLE users\n            DROP CONSTRAINT IF EXISTS check_3a60c18afc\n")
main:    -> 0.0005s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("            ALTER TABLE users\n            DROP CONSTRAINT IF EXISTS check_061f6f1c91\n")
main:    -> 0.0008s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("            ALTER TABLE users\n            DROP CONSTRAINT IF EXISTS check_c737c04b87\n")
main:    -> 0.0004s
main: == 20230823085824 AddNotNullToUsersColumns: reverted (0.0187s) ================

main: == [advisory_lock_connection] object_id: 224820, pg_backend_pid: 31542
ci: == [advisory_lock_connection] object_id: 224760, pg_backend_pid: 32339
ci: == 20230823085514 AddNotNullToUserPreferencesColumns: reverting ===============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("            ALTER TABLE user_preferences\n            DROP CONSTRAINT IF EXISTS check_d3248b1b9c\n")
ci:    -> 0.0008s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("            ALTER TABLE user_preferences\n            DROP CONSTRAINT IF EXISTS check_1d670edc68\n")
ci:    -> 0.0003s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("            ALTER TABLE user_preferences\n            DROP CONSTRAINT IF EXISTS check_b22446f91a\n")
ci:    -> 0.0005s
ci: == 20230823085514 AddNotNullToUserPreferencesColumns: reverted (0.0242s) ======

ci: == [advisory_lock_connection] object_id: 224760, pg_backend_pid: 32339
main: == [advisory_lock_connection] object_id: 224760, pg_backend_pid: 32759
main: == 20230823085514 AddNotNullToUserPreferencesColumns: reverting ===============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("            ALTER TABLE user_preferences\n            DROP CONSTRAINT IF EXISTS check_d3248b1b9c\n")
main:    -> 0.0008s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("            ALTER TABLE user_preferences\n            DROP CONSTRAINT IF EXISTS check_1d670edc68\n")
main:    -> 0.0004s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("            ALTER TABLE user_preferences\n            DROP CONSTRAINT IF EXISTS check_b22446f91a\n")
main:    -> 0.0003s
main: == 20230823085514 AddNotNullToUserPreferencesColumns: reverted (0.0164s) ======

main: == [advisory_lock_connection] object_id: 224760, pg_backend_pid: 32759

How to set up and validate locally

Run bin/rails db:migrate

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 #423001 (closed)

Edited by Abdul Wadood

Merge request reports