Fix SQL query for finding sequences owned by table

What does this MR do and why?

Previously the query for finding PostgreSQL sequences owned by a table erroneously picked up unrelated sequences. For some reason, after a pg_upgrade from PostgreSQL 13 to 14, some sequences appear to have new entries in the pg_depend table that are associated with p_ci_builds or ci_builds columns. As a result, the partitioning code would change the ownership to the wrong column. For example:

         seq_name          | table_name  |   col_name   | deptype
---------------------------+-------------+--------------+---------
 ci_builds_id_seq          | p_ci_builds | id           | a
 push_rules_id_seq         | ci_builds   | id           | n
 push_rules_id_seq         | ci_builds   | partition_id | n
 security_trainings_id_seq | p_ci_builds | id           | n
 security_trainings_id_seq | p_ci_builds | partition_id | n
 term_agreements_id_seq    | p_ci_builds | commit_id    | a
(6 rows)

Previously sequences_owned_by(p_ci_builds) would return the following items, even though the ownership for each of the items had not changed:

• ci_builds_id_seq
• security_trainings_id_seq
• term_agreements_id_seq

To avoid assigning sequences to the wrong owner, we need additional filtering for pg_class to ensure we only look at sequence owners.

Relates to:

• #473337 (closed)
• #468541 (closed)

Changelog: fixed

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

How to set up and validate locally

1. First, install both PostgreSQL 13.9 and 14.9 via asdf (e.g. asdf install postgres 13.9 && asdf install postgres 14.9). You may use any two major versions.

2. Then run:

export ASDF_POSTGRES_VERSION=13.9
initdb /tmp/data.13
curl -O https://gitlab.com/gitlab-org/gitlab/-/raw/16-11-stable-ee/db/structure.sql
postgres -D /tmp/data.13

3. In another terminal, load this schema:

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:

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 14 and run it:

export ASDF_POSTGRES_VERSION=14.9
initdb /tmp/data.14
pg_upgrade -b ~/.asdf/installs/postgres/13.9/bin -B ~/.asdf/installs/postgres/14.9/bin -d /tmp/data.13 -D /tmp/data.14
postgres -D /tmp/data.14

7. Now try the old query:

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)

8. Now try the new one:

gitlabhq_production=#  SELECT seq_pg_class.relname AS seq_name,
dep_pg_class.relname AS table_name,
pg_attribute.attname AS col_name
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 pg_depend.classid = 'pg_class'::regclass 
AND pg_depend.refclassid = 'pg_class'::regclass 
AND seq_pg_class.relkind = 'S' 
AND dep_pg_class.relname = 'p_ci_builds';
     seq_name     | table_name  | col_name
------------------+-------------+----------
 ci_builds_id_seq | p_ci_builds | id
(1 row)

You can confirm the ownership:

gitlabhq_production=# \set ECHO_HIDDEN on
gitlabhq_production=# \d+ dast_profiles_tags_id_seq;
<snip>

********* QUERY **********
SELECT pg_catalog.quote_ident(nspname) || '.' ||
   pg_catalog.quote_ident(relname) || '.' ||
   pg_catalog.quote_ident(attname),
   d.deptype
FROM pg_catalog.pg_class c
INNER JOIN pg_catalog.pg_depend d ON c.oid=d.refobjid
INNER JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
INNER JOIN pg_catalog.pg_attribute a ON (
 a.attrelid=c.oid AND
 a.attnum=d.refobjsubid)
WHERE d.classid='pg_catalog.pg_class'::pg_catalog.regclass
 AND d.refclassid='pg_catalog.pg_class'::pg_catalog.regclass
 AND d.objid='20631'
 AND d.deptype IN ('a', 'i')
**************************

                 Sequence "public.dast_profiles_tags_id_seq"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
Owned by: public.dast_profiles_tags.id