Schedule icon
Query icon
OutputValues icon
Log icon
If icon
SlackIncomingWebhook icon
Pause icon
Loop icon
Fail icon

PostgreSQL Pending Detach Partition Finalize Guard

Detect PostgreSQL partitions stuck pending detach after an interrupted concurrent detach, finalize them behind an approval gate, then re-read pg_inherits.

Categories
DataInfrastructure

ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY exists so a partition can leave a busy table without an exclusive lock on the parent. It does that by running in two phases: the first marks the partition as going away and commits, the second finishes the job once every transaction that could still use the old plan has ended. Interrupt the session between those phases, with a cancelled statement, a dropped connection or a deploy that killed the job. The partition is then stranded. pg_inherits.inhdetachpending stays true, so the partition is neither properly attached nor properly detached. The parent then refuses any further concurrent detach. The only way out is DETACH PARTITION ... FINALIZE, which almost nobody knows exists. This guard finds those partitions, finalizes them under a gate and proves the state cleared.

This blueprint was created by zkasuran.

Findings

Measured on PostgreSQL 16.15 while building this blueprint.

  • A stranded partition is invisible to the usual checks. pg_partition_tree() already leaves it out and the parent stops reading it, so a row count through the parent looks correct. Meanwhile the child still carries relispartition = true, keeps its partition bound plus its pg_inherits row. inhdetachpending is the only honest signal.
  • The operational bite lands on the next detach. With one partition pending, ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY on the same parent fails with partition "..." already pending detach in partitioned table "...". A retention job that detaches a partition a day is stuck from that moment on.
  • FINALIZE is not destructive. The child becomes an ordinary standalone table: relispartition flips to false, the partition bound is dropped, the pg_inherits row goes away and every row stays where it was. Verified at 1200 rows in and 1200 rows out.
  • Running FINALIZE while another session's concurrent detach is still waiting completes the detach under that session, which then fails with relation "..." is not a partition of relation "...". That is why this guard reads pg_stat_activity and leaves a pair alone while a concurrent detach for it is active.

Safety

  • Read-only by default. dry_run is true and auto_remediate is false, so a plain run reports and finalizes nothing.
  • A FINALIZE happens only when auto_remediate is true and dry_run is false. It then waits for a human to resume the Pause with approved set true. An unanswered gate reads as not approved.
  • No rows are lost. FINALIZE completes a detach, it does not drop anything. The partition ends up as a standalone table holding exactly the rows it held before. Dropping that table, archiving it or re-attaching it is a separate decision that stays with you.
  • FINALIZE is not completely free. It takes a brief ACCESS EXCLUSIVE lock on the parent and the child, which is why it sits behind the approval gate rather than running on a schedule.
  • A partition another session is detaching right now is reported and skipped, never finalized, so the guard does not pull the work out from under a live operation.
  • Scope is children of a real partitioned parent. Plain inheritance children, system catalogs, TOAST and temporary schemas are excluded.
  • The re-read of pg_inherits is the real gate, so a FINALIZE the connecting role is not allowed to run surfaces as a failed run.

How it works

  1. detect (io.kestra.plugin.jdbc.postgresql.Query) joins pg_inherits to pg_class, pg_namespace and pg_partitioned_table, keeps the rows where inhdetachpending is true and returns one row per stranded partition with its bound, its estimated row count, its on-disk size and the exact DETACH PARTITION FINALIZE statement built with format('%I'). The same query reads pg_stat_activity and fills live_pid when a concurrent detach for that exact parent and child is still running.
  2. summarize (io.kestra.plugin.core.output.OutputValues) splits the rows once into the finalizable set and the in-flight set, so every later step reads the same lists.
  3. log_detect (io.kestra.plugin.core.log.Log) logs both counts, the targets and the mode.
  4. guard (io.kestra.plugin.core.flow.If) branches on the count. With none found, clean logs and the run ends. Otherwise findings_alert posts the finding on every detection.
  5. mode (io.kestra.plugin.core.flow.If) enters the remediation branch only when auto_remediate is true, dry_run is false and at least one target is finalizable. Otherwise report_only lists what would be finalized and stops.
  6. approval_gate (io.kestra.plugin.core.flow.Pause) holds the run until a human resumes it. finalize_detaches (io.kestra.plugin.core.flow.Loop, concurrencyLimit: 1) then runs one ALTER TABLE ... DETACH PARTITION ... FINALIZE per target.
  7. reverify re-reads pg_inherits and verify_result fails the run through io.kestra.plugin.core.execution.Fail if any targeted partition is still pending detach. A partition stranded by somebody else during the run was never a target, so it does not fail the run.

What you get

  • A detector for a state no standard monitoring query reports, built on the one catalog column that actually carries it.
  • The exact remediation statement per partition, generated inside PostgreSQL so identifiers are quoted correctly.
  • A clear answer to the question an operator asks first: the rows survive, the partition becomes a standalone table, the follow-up DROP TABLE is yours to make.
  • A precision guard that leaves a partition alone while another session is still detaching it.
  • A real verification step. The run passes only when the second read of pg_inherits shows no target still pending.
  • Slack alerts on the findings and on any failure.

Who it's for

  • DBAs and platform engineers who run partition retention with concurrent detaches and have had one interrupted by a deploy, a cancelled query or a connection drop.
  • Teams whose nightly detach job started failing with "already pending detach" and who need the FINALIZE path found and run safely.
  • Teams evaluating the PostgreSQL plugin who want an example that reads the catalog, drives gated DDL and proves the state changed.

Why orchestrate this with Kestra

PostgreSQL leaves no trace of the interrupted statement, raises no alert and will not finish the job itself. The partition sits pending until somebody reads pg_inherits and knows the FINALIZE spelling. A cron job running FINALIZE blindly is the wrong answer, because the statement takes a short exclusive lock and can collide with a detach another session is still running. Kestra closes the gap. It schedules the sweep, builds each statement from the live catalog, gates the change behind dry-run and human approval, skips the pairs a live detach still owns, re-reads pg_inherits to turn a hopeful statement into a verified one, applies the same connection settings to every task and posts a Slack alert the moment a step fails.

Tested end to end

Run on Kestra 2.0.5 against PostgreSQL 16.15, with a local HTTP sink standing in for Slack. One schema held appd.events partitioned by month with three partitions, appd.orders partitioned by month with two, appd.metrics as a clean control, a plain inheritance parent and child, plus an ordinary table. Partitions were stranded by holding a read transaction on the parent, starting DETACH PARTITION ... CONCURRENTLY in a second session and cancelling that backend with pg_cancel_backend while it waited. Every count was confirmed with psql against pg_inherits.

  • Detector can fail. On the clean tree it returned 0 and took the clean branch. With two partitions stranded it returned exactly those two, never a healthy sibling partition, never the plain inheritance child and never the ordinary table.
  • Dry run reported both targets, alerted and left inhdetachpending true for both.
  • Remediate with approval ran both FINALIZE statements and the re-read reported 0 of the 2 still pending. Row counts went 1200 in and 1200 out on one target and 400 in and 400 out on the other, both now ordinary tables with relispartition false and no partition bound. The healthy sibling partitions stayed attached.
  • The parent that had refused DETACH PARTITION ... CONCURRENTLY with "already pending detach" accepted the same statement after the fix.
  • Declining the approval gate finalized nothing and left both partitions pending.
  • A role without ownership of the parent had its FINALIZE rejected, the re-read found the target still pending and the run failed and alerted rather than reporting success.
  • A partition with a concurrent detach still waiting in another session was reported as in-flight and skipped, so the run made no change to it.
  • An unreachable host failed the first query and fired the Slack alert.

Prerequisites

A PostgreSQL server reachable over JDBC, version 14 or later, since DETACH PARTITION CONCURRENTLY and FINALIZE arrive in 14. The connecting user must be able to read the catalog plus pg_stat_activity. To run FINALIZE it must also own the parent tables.

Secrets

  • POSTGRES_USERNAME: PostgreSQL user, read when use_secret_auth is true.
  • POSTGRES_PASSWORD: password for that user.
  • SLACK_WEBHOOK_URL: used by findings_alert and the errors block.
  • In Kestra OSS, provide each secret as a base64-encoded environment variable prefixed SECRET_.

Inputs

Name Type Default Description
postgres_url STRING jdbc:postgresql://localhost:5432/postgres PostgreSQL JDBC URL for the target database.
use_secret_auth BOOL true Read credentials from secrets. Set false only for inline test credentials.
postgres_username STRING postgres Username used only when use_secret_auth is false.
postgres_password STRING "" Password used only when use_secret_auth is false.
schema STRING "" Optional single schema filter on the parent table. Empty scans every user schema.
dry_run BOOL true Report without finalizing anything.
auto_remediate BOOL false Finalize behind the approval gate when dry_run is false.

Quick start

  1. Set POSTGRES_USERNAME, POSTGRES_PASSWORD and SLACK_WEBHOOK_URL as namespace secrets.
  2. Import this flow.
  3. Run it with postgres_url pointed at your database. The default dry run reports any partition stuck pending detach.
  4. To clear them, run again with dry_run false and auto_remediate true, then resume the paused execution with approved true.

How to extend

  • Set schema to restrict the sweep to one schema on a large database.
  • Fail the run as soon as detection finds anything, for a read-only drift check with no remediation.
  • Add a DROP TABLE step after the re-verify for partitions you know you want gone, once your retention policy says so.
  • Chain the retention job that detaches partitions behind this guard, so a stuck pending detach is cleared before the next detach is attempted.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.