Finalize pending detaches before dropping partitions

What does this MR do and why?

Context

Our goal is to reclaim space by detaching and dropping archived partitions of p_ci_job_definitions and p_ci_job_definition_instances. This MR is the next step toward it.

In the next MR, we plan to support DETACH PARTITION ... CONCURRENTLY in PartitionManager, opt-in per partitioning strategy. It detaches with a plain DETACH today, which holds ACCESS EXCLUSIVE on parent tables that CI writes to constantly.

Before we do that, we have to cover a gap that opens when a concurrent detach is interrupted. DETACH PARTITION ... CONCURRENTLY runs in two phases, and statement_timeout can cut it off between them. The partition is then left awaiting a FINALIZE. PostgreSQL has already removed it from the parent's partition descriptor, but the catalog still links it to that parent and relispartition stays true. postgres_partitions selects on relispartition, so the view reports the partition as attached.

On master, DetachedPartitionDropper deletes the bookkeeping record of any partition due to drop that still reports as attached. A partition awaiting FINALIZE reports as attached, so nothing would be left to drop it. We have to avoid this state.

Nothing detaches concurrently yet, so the state cannot arise today. We fix the dropper here so it is ready before the next MR can produce it.

This MR

• postgres_partitions exposes pending_detach, so callers can tell a partition awaiting FINALIZE from an attached one.
• The dropper handles three states. Fully detached, it drops. Awaiting FINALIZE, it finalizes and then drops. Attached, it behaves as before.

Changelog: other

References

• Task: Use `DETACH PARTITION CONCURRENTLY` in `Partiti... (#617929 - closed)
• Parent issue: Detach and drop archived partitions of p_ci_job... (#552078)

Database queries

See MR comments.

How to set up and validate locally

1. Run the migration:

   bundle exec rails db:migrate:up:main VERSION=20260831120000

   SELECT pending_detach FROM postgres_partitions LIMIT 1; succeeds.

2. Roll it back:

   bundle exec rails db:migrate:down:main VERSION=20260831120000

   The same query fails with column "pending_detach" does not exist.

3. Run the specs:

   bundle exec rspec spec/lib/gitlab/database/postgres_partition_spec.rb \
     spec/lib/gitlab/database/partitioning/detached_partition_dropper_spec.rb

MR acceptance checklist

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