Database upgrade from Postgres 14 to 16 fails due to lists table constraints
Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.
Summary
Attempting to upgrade our installed from source instance at https://invent.kde.org/ from Postgres 14 to Postgres 16 fails due to schema constraints.
Steps to reproduce
- Install Gitlab CE of an older version, using PostgreSQL 14.x as the database server
- Create a project, populating it with tasks and board(s) with at least one list on those boards.
- Upgrade to latest Gitlab CE.
- Attempt to upgrade from PostgreSQL 14 to 16.
Example Project
[Not relevant - self installed instances only]
What is the current bug behavior?
pg_restore: error: COPY failed for table "lists": ERROR: new row for relation "lists" violates check constraint "check_6dadb82d36"
DETAIL: Failing row contains (24424, 12048, null, 0, null, 2023-05-24 14:13:28.908841, 2023-05-24 14:13:28.908841, null, null, 0, 0, null, null, null, null).
CONTEXT: COPY lists, line 1: "24424 12048 \N 0 \N 2023-05-24 14:13:28.908841 2023-05-24 14:13:28.908841 \N \N 0 0 \N \N \N \N"Row appears to be the first row in the table when executing select * from lists so it is just symptomatic of the underlying issue.
What is the expected correct behavior?
Upgrade succeeds.
Relevant logs and/or screenshots
Appears to be a behaviour change - execution of the constraint condition against the table indicates all is well with Postgres 14.
gitlab_production=> select *, num_nonnulls(group_id, project_id) as check from lists where num_nonnulls(group_id, project_id) = 1;
id | board_id | label_id | list_type | position | created_at | updated_at | milestone_id | user_id | max_issue_count | max_issue_weight | limit_metric | iteration_id | group_id | project_id | check
----+----------+----------+-----------+----------+------------+------------+--------------+---------+-----------------+------------------+--------------+--------------+----------+------------+-------
(0 rows)Output of checks
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.6.5
Rake Version: 13.0.6
Redis Version: 7.4.2
Sidekiq Version:7.3.9
Go Version: go1.24.2 linux/amd64
GitLab information
Version: 17.11.0
Revision: 70a257393b7
Directory: /home/git/gitlab
DB Adapter: PostgreSQL
DB Version: 14.17
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.41.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-11691-gf6db088b9fb
- default Git Version: 2.49.0git@lerwini:~/gitlab$ bundle exec rake gitlab:check RAILS_ENV=production SANITIZE=true
Checking GitLab subtasks ...
Checking GitLab Shell ...
GitLab Shell: ... GitLab Shell version >= 14.41.0 ? ... OK (14.41.0)
Running /home/git/gitlab-shell/bin/gitlab-shell-check
Internal API available: OK
Redis available via internal API: OK
gitlab-shell self-check successful
Checking GitLab Shell ... Finished
Checking Gitaly ...
Gitaly: ... default ... OK
Checking Gitaly ... Finished
Checking Sidekiq ...
Sidekiq: ... Running? ... yes
Number of Sidekiq processes (cluster/worker) ... 1/1
Checking Sidekiq ... Finished
Checking Incoming Email ...
Incoming Email: ... Checking Reply by email ...
IMAP server credentials are correct? ... Checking gitlab yes
Mailroom enabled? ... no
Try fixing it:
Enable mail_room
For more information see:
doc/administration/reply_by_email.md
Please fix the error above and rerun the checks.
MailRoom running? ... can't check because of previous errors
Checking Reply by email ... Finished
Checking Incoming Email ... Finished
Checking LDAP ...
LDAP: ... Server: ldapmain
LDAP authentication... Success
LDAP users with access to your GitLab server (only showing the first 100 results)
User output sanitized. Found 100 users of 100 limit.
Checking LDAP ... Finished
Checking GitLab App ...
Database config exists? ... yes
Tables are truncated? ... skipped
All migrations up? ... yes
Database contains orphaned GroupMembers? ... no
GitLab config exists? ... yes
GitLab config up to date? ... yes
Cable config exists? ... yes
Resque config exists? ... yes
Log directory writable? ... yes
Tmp directory writable? ... yes
Uploads directory exists? ... yes
Uploads directory has correct permissions? ... yes
Uploads directory tmp has correct permissions? ... yes
Systemd unit files or init script exist? ... no
Try fixing it:
Install the Service
For more information see:
doc/install/installation.md in section "Install the Service"
Please fix the error above and rerun the checks.
Systemd unit files or init script up-to-date? ... can't check because of previous errors
Projects have namespace: ...
[Removed]
Redis version >= 6.2.14? ... yes
Ruby version >= 3.0.6 ? ... yes (3.2.5)
Git user has default SSH configuration? ... no
Try fixing it:
mkdir ~/gitlab-check-backup-1745307598
sudo mv /home/git/.ssh/id_rsa ~/gitlab-check-backup-1745307598
sudo mv /home/git/.ssh/id_rsa.pub ~/gitlab-check-backup-1745307598
sudo mv /home/git/.ssh/known_hosts.old ~/gitlab-check-backup-1745307598
For more information see:
doc/user/ssh.md#overriding-ssh-settings-on-the-gitlab-server
Please fix the error above and rerun the checks.
Active users: ... 4326
Is authorized keys file accessible? ... yes
GitLab configured to store new projects in hashed storage? ... yes
All projects are in hashed storage? ... yes
Checking GitLab App ... Finished
Checking GitLab subtasks ... FinishedResults of GitLab environment info
Upgrading from server 14.17 (Ubuntu 14.17-1.pgdg22.04+1) to 16.8 (Ubuntu 16.8-1.pgdg22.04+1)
Possible fixes
Unknown. Removal of constraint is a potential temporary workaround.
Edited by 🤖 GitLab Bot 🤖