feat(cli): backfill from the command line, and a plan that names the blocking detach

[AlexeyShalaev]

Why

Both changes come from one place: a real integration attempt against
hookdeck/outpost (main @ f67ae011), whose two
partitioned tables — events and attempts, both RANGE (time) — ship with a permanent
DEFAULT partition and nothing else. Running the released 1.4.0 against their unmodified
schema found exactly two things this library could not do for them, and both are generic.

1. The migration out of DEFAULT had no command line. Their maintainer's own open
   question on the growth issue is "is there a recommended migration path for existing
   DEFAULT partitions?" — and it was the one step an operator of a Go service could not
   run from the image. Creation walks forward from the cursor; the rows are behind it.
   Draining that DEFAULT needed a Python script, which is not something a Go project's
   operators are going to write.
2. The plan hedged on a lock it already knew about. Every retention detach on their
   schema logged DETACH PARTITION CONCURRENTLY failed; falling back — PostgreSQL refuses
   the concurrent form outright while a DEFAULT partition exists. The plan's lock text said
   ACCESS EXCLUSIVE on the parent when a DEFAULT partition forces the blocking form, a
   conditional whose answer was already in the catalog snapshot the plan was made from. A
   DBA approving a maintenance window on an ingestion table should not learn that from a
   runtime WARNING with no reason in its message.

What changed

fix(planner) — when the parent holds a DEFAULT partition, an AUTO detach is planned
as BLOCKING, and the operation's detail says why. plan --locks then names the
ACCESS EXCLUSIVE the run will really take, and the executor issues one statement instead
of one that fails and one that works. CONCURRENT is untouched: being refused is the
answer that mode exists to give. The runtime fallback stays for every other reason a server
can refuse the concurrent form.

feat(cli) — pg-partsmith backfill is partition_data from the command line.
docs/guide/cli.md said these verbs were library-only "because both want a progress story
a one-shot command does not have yet". This is that story: --max-batches N stops after N
statements per table and exits 2, every row already moved stays moved, and a Job can be
run until it exits 0:

until pg-partsmith backfill -c partitions.yaml --max-batches 50; do sleep 60; done

--output metrics carries pg_partsmith_backfilled_rows and
pg_partsmith_backfill_incomplete for the same reason. Hooks fire during backfill as
they do during apply — it creates partitions through the same executor — so the
--allow-hooks gate now covers both rather than letting a declared after_create pass
unnoticed.

unpartition stays library-only: it has a destination table to name and a drop_emptied
to decide, and neither has an obvious spelling on a command line yet.

The design I did not take

backfill could have been a backfill: N field on LifecyclePolicy — "also desire the N
windows behind the cursor" — composing with every creation policy and showing up in
plan/apply like anything else. I chose the command because draining a DEFAULT partition
is a one-off migration rather than a standing policy, because partition_data is what the
docs already recommend for a large DEFAULT (bounded batches, resumable) and a policy field
would have reached for ensure_partitions instead, and because it adds no new document
semantics to get wrong. Easy to redirect if you would rather have the field.

Evidence

Outpost's ten internal/migrator/migrations/postgres/*.up.sql applied verbatim to
PostgreSQL 17, seeded with 965 events and 965 attempts spanning 2026-01-05 → 2026-09-03,
every row in events_default / attempts_default. Ten lines of YAML, monthly, keep 6,
create 3 ahead. Run from the image built off this branch, nothing else installed:

$ backfill
public.events   — moved 965 rows in 9 batches into 9 partitions; DEFAULT is drained
public.attempts — moved 965 rows in 9 batches into 9 partitions; DEFAULT is drained

$ plan --table events
  DETACH public.events__2026_01 (retention_expired)      # ACCESS EXCLUSIVE on the parent
  ...
$ apply --allow-destructive
public.events   — created 2, repaired 0, attached 0, detached 3, dropped 3
public.attempts — created 2, repaired 0, attached 0, detached 3, dropped 3

965 → 621 rows, exactly the 344 of 2026-01..03; events_default empty; no fallback
WARNING on stderr. Resumability checked separately: --max-batches 3 moved 344 rows and
exited 2 with 621 left, and the loop above finished it with all 965 rows intact.

Tests

make check clean. pytest — 3358 passed, 20 skipped, coverage 97.96%. New:

• planner unit: AUTO under a DEFAULT is planned blocking with the reason and the lock
  text; CONCURRENT is not downgraded; the existing test asserting AUTO stays AUTO
  without a DEFAULT is the negative control.
• integration, both mirrors: the same on real PostgreSQL, asserting the fallback WARNING is
  absent and the outcome unchanged, plus a new no-DEFAULT case.
• CLI unit: exit OK / DRIFT / FINDINGS, the flags reaching partition_data, the
  metrics, and the --allow-hooks refusal.
• CLI integration: rows leave DEFAULT into their windows; --max-batches exits 2 and the
  next run carries on with nothing lost.

make test-e2e not run locally (CI builds the image); the image was built from this branch
by hand for the evidence above.

One behaviour change worth naming

tests/integration/{aio,sync}/test_lifecycle_policies.py::test__detach_mode_auto__with_a_default_partition__falls_back_to_the_blocking_form
asserted the WARNING. It is rewritten to assert its absence. Anyone alerting on that log
line for DEFAULT-partitioned tables will stop seeing it — the detach they were watching now
succeeds first time. Same end state, one fewer failed statement per partition.

[codecov]

Codecov Report

✅ All modified and coverable lines are covered by tests.

📢 Thoughts on this report? Let us know!