Postgres Database Migration: 14 to 16 fails when top level snippets have comments

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

Summary

Postgres database updates from 14.13 to 16.4 will fail during the upgrade process due to database constraints being violated.

Steps to reproduce

  1. Create a snippet in the top level snippets area
  2. Attempt to perform an upgrade from Postgres 14.13 to 16.4 and observe a failure message during the upgrade.

Following failure, rollback to 14.13.

Example Project

Not relevant

What is the current bug behavior?

Database upgrade fails due to database check constraint violation on the events table.

What is the expected correct behavior?

Upgrade should complete successfully.

Relevant logs and/or screenshots

pg_restore: error: COPY failed for table "events": ERROR:  new row for relation "events" violates check constraint "check_events_sharding_key_is_not_null"
DETAIL:  Failing row contains (null, 111, 2024-05-04 18:41:10.956253+02, 2024-05-04 18:41:10.956253+02, 6, Note, null, null, 1775770, 936274, 0, null).
CONTEXT:  COPY events, line 1992: "\N   111     2024-05-04 18:41:10.956253+02   2024-05-04 18:41:10.956253+02   6       Note    \N      \N      1775770 936274  0       \N"
pg_restore: warning: errors ignored on restore: 1

Output of checks

Results of GitLab environment info

Installation from source.

Expand for output related to GitLab environment info 

System information \
System:         Ubuntu 22.04 \
Current User:   git \
Using RVM:      no \
Ruby Version:   3.2.5 \
Gem Version:    3.5.22 \
Bundler Version:2.5.11 \
Rake Version:   13.0.6 \
Redis Version:  7.4.1 \
Sidekiq Version:7.2.4 \
Go Version:     go1.23.2 linux/amd64 \
\
GitLab information \
Version:        17.5.1 \
Revision:       9375a01edff \
Directory:      /home/git/gitlab \
DB Adapter:     PostgreSQL \
DB Version:     14.13 \
URL:            https://invent.kde.org \
HTTP Clone URL: https://invent.kde.org/some-group/some-project.git \
SSH Clone URL:  git@invent.kde.org:some-group/some-project.git \
Using LDAP:     yes \
Using Omniauth: no \
\
GitLab Shell \
Version:        14.39.0 \
Repository storages: \
- default:      unix:/home/git/gitlab/tmp/sockets/private/gitaly.socket \
GitLab Shell path:              /home/git/gitlab-shell \
\
Gitaly \
- default Address:      unix:/home/git/gitlab/tmp/sockets/private/gitaly.socket \
- default Version:      14.3.0-rc1-10674-g42b2dc21d50 \
- default Git Version:  2.47.0

Results of GitLab application Check

Check appears normal when run on console.

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

Looks like migrations may not have picked up all edge cases? In our case it looks like a manual SQL update to change the relevant row in the events table to set personal_namespace_id = 111 for the line in question should correct the issue - however only raising this because we don't make any direct database modifications so this shouldn't have been possible.

The snippet in question is http://invent.kde.org/-/snippets/3109 that is triggering this.

Edited by 🤖 GitLab Bot 🤖