I've noticed that after running `pg_upgrade` that my `pg_depend` table
contains unexpected dependencies for sequences. Before the upgrade
from PostgreSQL 15.7:
```
% psql -d gitlabhq_production
psql (16.3, server 15.7)
Type "help" for help.
gitlabhq_production=# SELECT seq_pg_class.relname AS seq_name,
dep_pg_class.relname AS table_name,
pg_attribute.attname AS col_name,
pg_depend.classid,
classid_class.relname AS classid_relname,
pg_depend.refclassid,
refclassid_class.relname AS refclassid_relname
FROM pg_class seq_pg_class
INNER JOIN pg_depend ON seq_pg_class.oid = pg_depend.objid
INNER JOIN pg_class dep_pg_class ON pg_depend.refobjid = dep_pg_class.oid
INNER JOIN pg_attribute ON dep_pg_class.oid = pg_attribute.attrelid
AND pg_depend.refobjsubid = pg_attribute.attnum
INNER JOIN pg_class classid_class ON pg_depend.classid = classid_class.oid
INNER JOIN pg_class refclassid_class ON pg_depend.refclassid =
refclassid_class.oid
WHERE seq_pg_class.relkind = 'S'
AND (dep_pg_class.relname = 'p_ci_builds' OR dep_pg_class.relname =
'ci_builds');
seq_name | table_name | col_name | classid | classid_relname
| refclassid | refclassid_relname
------------------+-------------+----------+---------+-----------------+------------+--------------------
ci_builds_id_seq | p_ci_builds | id | 1259 | pg_class
| 1259 | pg_class
(1 row)
```
After the upgrade to PostgreSQL 16.3, I see these dependencies:
```
seq_name | table_name | col_name
| classid | classid_relname | refclassid | refclassid_relname
-----------------------------------------+-------------+--------------+---------+-----------------+------------+--------------------
ci_builds_id_seq | p_ci_builds | id
| 1259 | pg_class | 1259 | pg_class
note_metadata_note_id_seq | ci_builds | stage_id
| 2606 | pg_constraint | 1259 | pg_class
note_metadata_note_id_seq | ci_builds | partition_id
| 2606 | pg_constraint | 1259 | pg_class
project_repository_storage_moves_id_seq | ci_builds | id
| 2606 | pg_constraint | 1259 | pg_class
project_repository_storage_moves_id_seq | ci_builds | partition_id
| 2606 | pg_constraint | 1259 | pg_class
x509_commit_signatures_id_seq | ci_builds | id
| 2606 | pg_constraint | 1259 | pg_class
x509_commit_signatures_id_seq | ci_builds | partition_id
| 2606 | pg_constraint | 1259 | pg_class
(7 rows)
```
What's odd is that the `pg_constraint` entries don't seem to be
deterministic: I often see different entries every time I run
`pg_upgrade`.
Are these entries expected to be there, or is this a bug?
Here's what I did to reproduce. I use `asdf` to manage multiple
versions, so I used the ASDF_POSTGRES_VERSION environment variable to
override which version to use:
1. First, install both PostgreSQL 15.7 and 16.3 via `asdf` (e.g. `asdf
install postgres 15.7 && asdf install postgres 16.3`). You may use any
two major versions.
2. Then run:
```shell
export ASDF_POSTGRES_VERSION=15.7
initdb /tmp/data.15
curl -O https://gitlab.com/gitlab-org/gitlab/-/raw/16-11-stable-ee/db/structure.sql
postgres -D /tmp/data.15
```
3. In another terminal, load this schema:
```shell
psql -d template1 -c 'create database gitlabhq_production'
psql -d gitlabhq_production < structure.sql
```
4. Check the constraints that `ci_builds_id_seq` is the only entry:
```sql
psql -d gitlabhq_production
<snip>
gitlabhq_production=# SELECT seq_pg_class.relname AS seq_name,
dep_pg_class.relname AS table_name, pg_attribute.attname AS col_name,
deptype
FROM pg_class seq_pg_class
INNER JOIN pg_depend ON seq_pg_class.oid = pg_depend.objid
INNER JOIN pg_class dep_pg_class ON pg_depend.refobjid = dep_pg_class.oid
INNER JOIN pg_attribute ON dep_pg_class.oid = pg_attribute.attrelid
AND pg_depend.refobjsubid = pg_attribute.attnum
WHERE seq_pg_class.relkind = 'S'
dep_pg_class.relname = 'p_ci_builds';
seq_name | table_name | col_name | deptype
------------------+-------------+----------+---------
ci_builds_id_seq | p_ci_builds | id | a
(1 row)
```
5. Terminate `postgres` in the other window.
6. Now let's upgrade to PostgreSQL 16 and run the database:
```shell
export ASDF_POSTGRES_VERSION=16.3
initdb /tmp/data.16
pg_upgrade -b ~/.asdf/installs/postgres/15.7/bin -B
~/.asdf/installs/postgres/16.3/bin -d /tmp/data.15 -D /tmp/data.16
postgres -D /tmp/data.16
```
7. Now try the query and see the new entries:
```sql
gitlabhq_production=# SELECT seq_pg_class.relname AS seq_name,
dep_pg_class.relname AS table_name, pg_attribute.attname AS col_name,
deptype
FROM pg_class seq_pg_class
INNER JOIN pg_depend ON seq_pg_class.oid = pg_depend.objid
INNER JOIN pg_class dep_pg_class ON pg_depend.refobjid = dep_pg_class.oid
INNER JOIN pg_attribute ON dep_pg_class.oid = pg_attribute.attrelid
AND pg_depend.refobjsubid = pg_attribute.attnum
WHERE seq_pg_class.relkind = 'S'
AND (dep_pg_class.relname = 'ci_builds' OR dep_pg_class.relname =
'p_ci_builds');
seq_name | table_name |
col_name | deptype
------------------------------------------------+-------------+-------------------+---------
ci_builds_id_seq | p_ci_builds | id
| a
dast_profiles_tags_id_seq | p_ci_builds | id
| a
dast_profiles_tags_id_seq | p_ci_builds |
partition_id | a
merge_request_diff_commit_users_id_seq | p_ci_builds |
resource_group_id | a
ml_models_id_seq | ci_builds | id
| n
ml_models_id_seq | ci_builds |
partition_id | n
packages_debian_group_distribution_keys_id_seq | ci_builds | id
| n
packages_debian_group_distribution_keys_id_seq | ci_builds |
partition_id | n
pages_deployments_id_seq | ci_builds | id
| n
pages_deployments_id_seq | ci_builds |
partition_id | n
project_repositories_id_seq | p_ci_builds | id
| n
project_repositories_id_seq | p_ci_builds |
partition_id | n
user_custom_attributes_id_seq | ci_builds | id
| n
user_custom_attributes_id_seq | ci_builds |
partition_id | n
(14 rows)
```
