Fill null values of user_preferences table with their default values

What does this MR do and why?

This MR is step 2 of making a column NOT NULL according to our database guidelines. We have completed the step 1 in !102897 (diffs) by adding default attributes.

Currently, we're using getters and setters in the UserPreference model to use default values but it's a hacky way to deal with default values. We want to make these columns NOT NULL having default values. We have already added default values to these columns in !122467 (diffs).

Summary of changes

MR	Step
Fill null values of user_preferences table with... (!125774 - merged)	1 👈 You are here
Finalize the background migration (TBD)	.2
Validate the presence of default User and UserP... (!127587 - merged)	3
Prevent adding new records with null attributes (!125744 - merged)	4

Query plans

Query 1

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/20218/commands/65948

Raw querỷ

UPDATE "user_preferences"
SET "tab_width" = 8
WHERE "user_preferences"."id" BETWEEN 10000 AND 13000
  AND (tab_width IS NULL OR time_display_relative IS NULL OR render_whitespace_in_code IS NULL)
  AND "user_preferences"."id" >= 10000
  AND "user_preferences"."id" < 10200
  AND "user_preferences"."tab_width" IS NULL;

Query 2

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/20227/commands/66039

Raw querỷ

UPDATE "user_preferences"
SET "time_display_relative" = TRUE
WHERE "user_preferences"."id" BETWEEN 10000 AND 13000
  AND (tab_width IS NULL OR time_display_relative IS NULL OR render_whitespace_in_code IS NULL)
  AND "user_preferences"."id" >= 10000
  AND "user_preferences"."id" < 10200
  AND "user_preferences"."time_display_relative" IS NULL;

Query 3

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/20218/commands/65952

Raw querỷ

UPDATE "user_preferences"
SET "render_whitespace_in_code" = FALSE
WHERE "user_preferences"."id" BETWEEN 10000 AND 13000
  AND (tab_width IS NULL OR time_display_relative IS NULL OR render_whitespace_in_code IS NULL)
  AND "user_preferences"."id" >= 10000
  AND "user_preferences"."id" < 10200
  AND "user_preferences"."render_whitespace_in_code" IS NULL;

Migration output

up

bin/rails db:migrate
main: == [advisory_lock_connection] object_id: 223660, pg_backend_pid: 34078
main: == 20230707131138 QueueBackfillUserPreferencesWithDefaults: migrating =========
main: == 20230707131138 QueueBackfillUserPreferencesWithDefaults: migrated (0.1388s)

main: == [advisory_lock_connection] object_id: 223660, pg_backend_pid: 34078
ci: == [advisory_lock_connection] object_id: 224140, pg_backend_pid: 34080
ci: == 20230707131138 QueueBackfillUserPreferencesWithDefaults: 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: == 20230707131138 QueueBackfillUserPreferencesWithDefaults: migrated (0.0105s)

ci: == [advisory_lock_connection] object_id: 224140, pg_backend_pid: 34080

down

bin/rails db:rollback:ci && bin/rails db:rollback:main
ci: == [advisory_lock_connection] object_id: 223400, pg_backend_pid: 30181
ci: == 20230707131138 QueueBackfillUserPreferencesWithDefaults: 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: == 20230707131138 QueueBackfillUserPreferencesWithDefaults: reverted (0.0097s)

ci: == [advisory_lock_connection] object_id: 223400, pg_backend_pid: 30181
main: == [advisory_lock_connection] object_id: 223360, pg_backend_pid: 30597
main: == 20230707131138 QueueBackfillUserPreferencesWithDefaults: reverting =========
main: == 20230707131138 QueueBackfillUserPreferencesWithDefaults: reverted (0.0327s)

main: == [advisory_lock_connection] object_id: 223360, pg_backend_pid: 30597

How to set up and validate locally

1. Check the count of records having the related columns as NULL: UserPreference.where('tab_width IS NULL OR time_display_relative IS NULL OR render_whitespace_in_code IS NULL').count
2. Run the migration
3. Check the count again and the result should be zero.

MR acceptance checklist

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

• I have evaluated the MR acceptance checklist for this MR.

Related to #388393 (closed)