Fill null values of users 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 default attributes in the model. It would be better to have their default set at the database level as well.

Summary of changes

MR	Step
Fill null values of users table with their defa... (!125881 - 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/20227/commands/66072

Raw querỷ

UPDATE "users"
SET "project_view" = 2
WHERE "users"."id" BETWEEN 10000 AND 13000
  AND (project_view IS NULL OR hide_no_ssh_key IS NULL OR hide_no_password IS NULL OR notified_of_own_activity IS NULL)
  AND "users"."id" >= 10000
  AND "users"."id" < 10200
  AND "users"."project_view" IS NULL;

Query 2

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

Raw querỷ

UPDATE "users"
SET "hide_no_ssh_key" = FALSE
WHERE "users"."id" BETWEEN 10000 AND 13000
  AND (project_view IS NULL OR hide_no_ssh_key IS NULL OR hide_no_password IS NULL OR notified_of_own_activity IS NULL)
  AND "users"."id" >= 10000
  AND "users"."id" < 10200
  AND "users"."hide_no_ssh_key" IS NULL;

Query 3

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

Raw querỷ

UPDATE "users"
SET "hide_no_password" = FALSE
WHERE "users"."id" BETWEEN 10000 AND 13000
  AND (project_view IS NULL OR hide_no_ssh_key IS NULL OR hide_no_password IS NULL OR notified_of_own_activity IS NULL)
  AND "users"."id" >= 10000
  AND "users"."id" < 10200
  AND "users"."hide_no_password" IS NULL;

Query 4

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

Raw querỷ

UPDATE "users"
SET "notified_of_own_activity" = FALSE
WHERE "users"."id" BETWEEN 10000 AND 13000
  AND (project_view IS NULL OR hide_no_ssh_key IS NULL OR hide_no_password IS NULL OR notified_of_own_activity IS NULL)
  AND "users"."id" >= 10000
  AND "users"."id" < 10200
  AND "users"."notified_of_own_activity" IS NULL;

Migration output

up

bin/rails db:migrate
main: == [advisory_lock_connection] object_id: 223780, pg_backend_pid: 9309
main: == 20230710104226 QueueBackfillUsersWithDefaults: migrating ===================
main: == 20230710104226 QueueBackfillUsersWithDefaults: migrated (0.0464s) ==========

main: == [advisory_lock_connection] object_id: 223780, pg_backend_pid: 9309
ci: == [advisory_lock_connection] object_id: 224240, pg_backend_pid: 9311
ci: == 20230710104226 QueueBackfillUsersWithDefaults: 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: == 20230710104226 QueueBackfillUsersWithDefaults: migrated (0.0117s) ==========

ci: == [advisory_lock_connection] object_id: 224240, pg_backend_pid: 9311

down

bin/rails db:rollback:ci && bin/rails db:rollback:main
ci: == [advisory_lock_connection] object_id: 223400, pg_backend_pid: 10094
ci: == 20230710104226 QueueBackfillUsersWithDefaults: 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: == 20230710104226 QueueBackfillUsersWithDefaults: reverted (0.0111s) ==========

ci: == [advisory_lock_connection] object_id: 223400, pg_backend_pid: 10094
main: == [advisory_lock_connection] object_id: 223400, pg_backend_pid: 10642
main: == 20230710104226 QueueBackfillUsersWithDefaults: reverting ===================
main: == 20230710104226 QueueBackfillUsersWithDefaults: reverted (0.0375s) ==========

main: == [advisory_lock_connection] object_id: 223400, pg_backend_pid: 10642

How to set up and validate locally

1. Check the count of records having the related columns as NULL:

User.where(
  'project_view IS NULL OR ' \
  'hide_no_ssh_key IS NULL OR ' \
  'hide_no_password IS NULL OR ' \
  'notified_of_own_activity 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)