pg_upgrade adds unexpected pg_constraint entries to pg_depend - Mailing list pgsql-hackers

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)
```