Skip to content

Prevents note column swap migration to fail

What does this MR do and why?

Prevents note column swap migration from failing.

Some customers reported that their instances failed to upgrade to v16.4.0 because their database have a different name for fk_d83a918cb1 FK.

Column swap will not be completed if notes_pkey is still dependent on FKs in the database. To avoid issues, the migration will look up for these faulting FK and drop them.

How to set up and validate locally

  1. Install Multipass to emulate an Ubuntu VM

  2. Create Ubuntu VM

multipass launch jammy --name gitlab-omnibus --memory 8G --disk 20G
  1. SSH to the VM
multipass shell gitlab-omnibus
  1. Start from GitLab v15.11.13
sudo apt-get update
sudo apt-get install -y curl openssh-server ca-certificates tzdata perl postfix
curl | sudo bash
# EXTERNAL_URL should be http, so we do not have to bother with SSL
sudo EXTERNAL_URL="" apt-get install gitlab-ee=15.11.13-ee.0 # (
  1. Access psql and rename fk_d83a918cb1
sudo gitlab-psql
ALTER TABLE system_note_metadata RENAME CONSTRAINT "fk_d83a918cb1" TO "fk_d83a918cb1_renamed";
  1. Upgrade to 16.3.4
sudo apt update && sudo apt install gitlab-ee=16.3.4-ee.0
  1. Upgrade to 16.4.0
sudo apt update && sudo apt install gitlab-ee=16.4.0-ee.0
  1. Upgrade should fail
PG::DependentObjectsStillExist: ERROR:  cannot drop constraint notes_pkey on table notes because other objects depend on it
DETAIL:  constraint fk_d83a918cb1_renamed on table system_note_metadata depends on index notes_pkey
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20230823145126_swap_notes_id_to_bigint_for_self_managed.rb:111:in `block in swap'
  1. Update the source code

Apply this branch changes to 20230823145126_swap_notes_id_to_bigint_for_self_managed migration file

sudo vim /opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20230823145126_swap_notes_id_to_bigint_for_self_managed.rb
  1. Run migrations
sudo gitlab-rails db:migrate

Then, FK should be now, fixed:

sudo gitlab-psql

gitlabhq_production=# \d system_note_metadata

                                                Table "public.system_note_metadata"
          Column           |            Type             | Collation | Nullable |                     Default
 id                        | integer                     |           | not null | nextval('system_note_metadata_id_seq'::regclass)
Foreign-key constraints:
    "fk_d83a918cb1" FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE
    "fk_fbd87415c9" FOREIGN KEY (description_version_id) REFERENCES description_versions(id) ON DELETE SET NULL
Referenced by:

And was converted to BIGINT:

gitlabhq_production=# \d notes
                                               Table "public.notes"
         Column          |            Type             | Collation | Nullable |              Default
 id_convert_to_bigint    | integer                     |           | not null | 0
 id                      | bigint                      |           | not null | nextval('notes_id_seq'::regclass)
  1. Upgrade to v16.4.0
sudo apt update && sudo apt install gitlab-ee=16.4.0-ee.0

The upgrade should be successful now.

  1. Erase the VM
multipass delete --purge gitlab-omnibus

Related to omnibus-gitlab#8227

Edited by Leonardo da Rosa

Merge request reports