16.2+ upgrade fails with database VALIDATE CONSTRAINT error for check constraint "check_0dd5948e38"

Summary

This is a placeholder issue, linked from the upgrade notes in the documentation.

Upgrading to GitLab 16.2 or later fails with error:

main: == 20230620134708 ValidateUserTypeConstraint: migrating =======================
main: -- execute("ALTER TABLE users VALIDATE CONSTRAINT check_0dd5948e38;")
rake aborted!
StandardError: An error has occurred, all later migrations canceled:

PG::CheckViolation: ERROR:  check constraint "check_0dd5948e38" of relation "users" is violated by some row

It was observed in a test environment and so might occur in customer environments as well.

This constraint assures that all values in the user_type column are not NULL

• 16.0 introduced a batch background migration to fix the rows in the table.
• 16.1 finalised this migration
• 16.2 then validates the constraint

For there to be rows with NULL values in this column, either the migration didn't complete (properly) or records were inserted (prior to the NOT VALID constraint being put in place) that somehow didn't get scoped into the migration.

Workaround

• check how many records have NULL values

  SELECT count(*) FROM users WHERE user_type IS NULL;

  If it's a lot of records, updating them all in one go is inadvisable. It may causing locking on the table. The batch size on the 16.0 migration was 2,000, so this seems like the threshold for attempting to fix this.

• update all records that have NULL values, setting 0 instead.

  SET statement_timeout = 0 is used to ensure it can run for as long as needed, but if it's needed, then this UPDATE is going to run for too long.

  SET statement_timeout = 0;
  UPDATE users SET user_type = 0 WHERE user_type IS NULL;

• complete the migrations including the constraint

  sudo gitlab-rake db:migrate
  

• complete any upgrade steps - Linux package (Omnibus)

  sudo gitlab-ctl reconfigure

• restart Puma and Sidekiq

  sudo gitlab-ctl restart sidekiq
  sudo gitlab-ctl restart puma

Steps to reproduce

Example Project

What is the current bug behavior?

the constraint fails to apply

What is the expected correct behavior?

The constraint applies.

Relevant logs and/or screenshots

Output of checks

Results of GitLab environment info

Expand for output related to GitLab environment info

(For installations with omnibus-gitlab package run and paste the output of:
`sudo gitlab-rake gitlab:env:info`)

(For installations from source run and paste the output of:
`sudo -u git -H bundle exec rake gitlab:env:info RAILS_ENV=production`)

Results of GitLab application Check

Expand for output related to the GitLab application check

(For installations with omnibus-gitlab package run and paste the output of:
sudo gitlab-rake gitlab:check SANITIZE=true)
(For installations from source run and paste the output of:
sudo -u git -H bundle exec rake gitlab:check RAILS_ENV=production SANITIZE=true)
(we will only investigate if the tests are passing)

Possible fixes