Guard Apify scrapes with cost caps and an automated quality gate
Run an Apify actor under hard cost and run caps, then branch on the returned item count so a degraded scrape pages the on-call instead of silently shipping bad data.
Detect PostgreSQL partitions stuck pending detach after an interrupted concurrent detach, finalize them behind an approval gate, then re-read pg_inherits.
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.
Measured on PostgreSQL 16.15 while building this blueprint.
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.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.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.dry_run is true and auto_remediate is false, so a plain run reports and finalizes nothing.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.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.pg_inherits is the real gate, so a FINALIZE the connecting role is not allowed to run surfaces as a failed run.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.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.log_detect (io.kestra.plugin.core.log.Log) logs both counts, the targets and the mode.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.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.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.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.DROP TABLE is yours to make.pg_inherits shows no target still pending.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.
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.
inhdetachpending true for 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.DETACH PARTITION ... CONCURRENTLY with "already pending detach" accepted the same statement after the fix.FINALIZE rejected, the re-read found the target still pending and the run failed and alerted rather than reporting success.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.
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.SECRET_.| 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. |
POSTGRES_USERNAME, POSTGRES_PASSWORD and SLACK_WEBHOOK_URL as namespace secrets.postgres_url pointed at your database. The default dry run reports any partition stuck pending detach.dry_run false and auto_remediate true, then resume the paused execution with approved true.schema to restrict the sweep to one schema on a large database.DROP TABLE step after the re-verify for partitions you know you want gone, once your retention policy says so.