Remove partition if exists for pm_package tables

What does this MR do and why?

Fix for 2023-02-17: dev.gitlab.org is down (gitlab-com/gl-infra/production#8414 - closed)

Timeline:

• !104155 (merged) added a migration to partition pm_packages, pm_package_versions, and pm_package_version_licenses.
• The migration was deployed to staging, but not to production.
• !108644 (merged) removed that migration before it run on production
• !108722 (merged) adds removed migration back as an empty migration, to ensure that the same migrations run on every environment
• !108702 (merged) removes partition for staging and local env
• !108600 (merged) add another migration that drop constraint that doesn't exists on dev.gitlab.org
• 2023-02-17: dev.gitlab.org is down (gitlab-com/gl-infra/production#8414 - closed) is created as previous migration fails on dev

At this point we have environments in 2 different states:

• partitioning reverted
  • local
  • staging
  • prod
• partitioning still exists (because it doesn't fall under the clause specified in the revert !108702 (diffs))
  • staging-ref
  • dev

The still-partitioned environments stopped their migrations on an error in this migration: db/migrate/20230127155217_add_id_column_to_package_metadata_join_table.rb

The fix is to edit this migration and conditionally update the partitioning if it exists. After that proceed with the migration as before.

How to set up and validate locally

To reproduce what would happen in {local, staging, prod} environments

export CHANGE_PRIMARY_KEY=20230127155217
bundle exec rake db:migrate:{down,up}:main VERSION=$CHANGE_PRIMARY_KEY

# there should be no error and the primary key is updated
gdk psql -c '\d pm_package_version_licenses' | grep 'PRIMARY KEY'
# returns: "pm_package_version_licenses_pkey" PRIMARY KEY, btree (id)

Output: run-migration-on-unpartitioned.log.txt

To reproduce what would happen in {staging-ref, dev} environments

Because the partitioning MR was removed to make a no-op we need to checkout the right version of the migration: git checkout 754f9def18bca3c5a01131ed037c9e887d31a43d -- db/migrate/20221215210347_partition_pm_package_metadata_tables.rb

# checkout the original version that makes the partitioned tables
git checkout 754f9def18bca3c5a01131ed037c9e887d31a43d -- db/migrate/20221215210347_partition_pm_package_metadata_tables.rb

export CREATE_PARTITIONS=20221215210347
export CHANGE_PRIMARY_KEY=20230127155217

bundle exec rake db:migrate:down:main VERSION=$CHANGE_PRIMARY_KEY

# partition
bundle exec rake db:migrate:{down,up}:main VERSION=$CREATE_PARTITIONS

# verify partitions created
gdk psql -c '\d pm_package_version_licenses' | grep 'Number of partitions'
# returns: Number of partitions: 8 (Use \d+ to list them.)

# we are now at the stage as dev and stage-ref are before the migration ran initially

# now change primary key
bundle exec rake db:migrate:up:main VERSION=$CHANGE_PRIMARY_KEY

# no errors should be thrown and migration should have worked with a new primary key on the id column
gdk psql -c '\d pm_package_version_licenses' | grep 'PRIMARY KEY'
# returns: "pm_package_version_licenses_pkey" PRIMARY KEY, btree (id)

# partitions are removed
gdk psql -c '\d pm_package_version_licenses' | grep 'Number of partitions'; echo $?
# returns: 1

Output: run-migration-on-partitioned.log.txt

To reproduce the error

we need to remove the guard code on !112310 (diffs) and run the above steps again

# editing db/migrate/20230127155217_add_id_column_to_package_metadata_join_table.rb

# change
if Gitlab::Database::PostgresPartitionedTable.where(name: 'pm_package_version_licenses').exists?

# to
if false && Gitlab::Database::PostgresPartitionedTable.where(name: 'pm_package_version_licenses').exists?

# checkout the original version
git checkout 754f9def18bca3c5a01131ed037c9e887d31a43d -- db/migrate/20221215210347_partition_pm_package_metadata_tables.rb

export CREATE_PARTITIONS=20221215210347
export CHANGE_PRIMARY_KEY=20230127155217

bundle exec rake db:migrate:down:main VERSION=$CHANGE_PRIMARY_KEY

# partition
bundle exec rake db:migrate:{down,up}:main VERSION=$CREATE_PARTITIONS

# verify partitions created
gdk psql -c '\d pm_package_version_licenses' | grep 'Number of partitions'
# returns: Number of partitions: 8 (Use \d+ to list them.)

# now run the migration -- the missing primary key error is thrown
bundle exec rake db:migrate:up:main VERSION=$CHANGE_PRIMARY_KEY

Output: reproduce-error.log.txt

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.