Real-Time Kafka DLQ Quarantine and Remediation with ClickHouse
Stream poisoned Kafka DLQ records into ClickHouse with automated error classification and rate-limited Slack burst alerting.
Roll back orphaned PostgreSQL 2PC prepared transactions with Kestra. Release pinned xmin horizons, unblock autovacuum, and verify rollback safely.
When distributed transaction managers, microservices, or external orchestrators use two-phase commits (2PC) via PREPARE TRANSACTION, prepared transactions are written to disk in pg_twophase. Unlike standard backend connections, prepared transactions survive client disconnects, network drops, and database restarts. If a distributed coordinator crashes or loses track of a transaction before issuing a commit or rollback, that transaction remains open indefinitely.
An orphaned prepared transaction holds exclusive locks on modified rows and tables, blocking subsequent DDL and conflicting writes. More critically, an open prepared transaction pins the cluster-wide transaction horizon (xmin). As long as an old prepared transaction exists, PostgreSQL autovacuum cannot reclaim dead tuples generated after that transaction across any table in the database. This leads to runaway table and index bloat, disk exhaustion, degraded sequential scan performance, and eventually forces the cluster into emergency read-only mode to prevent transaction ID wraparound.
This blueprint audits pg_prepared_xacts for transactions exceeding an operational age threshold. When abandoned branches are detected, it alerts Slack with the exact identifiers, enforces a safe rollback ceiling, gates destructive action behind human approval (Pause), sequentially executes ROLLBACK PREPARED, and re-queries PostgreSQL to verify the catalog has cleared.
detect (io.kestra.plugin.jdbc.postgresql.Query) queries pg_prepared_xacts for transactions older than min_age_minutes in current_database(), assembling ROLLBACK PREPARED 'gid'; statements via PostgreSQL's built-in quote_literal() function.summarize (io.kestra.plugin.core.output.OutputValues) builds human-readable Slack text and JSON payloads for alerts and loops.log_detect (io.kestra.plugin.core.log.Log) logs detection counts, transaction identifiers, and parameters.guard (io.kestra.plugin.core.flow.If) branches on detection:findings_alert alerts Slack. ceiling_check confirms count <= max_rollbacks. When auto_remediate: true and dry_run: false, approval_gate pauses for operator approval.rollback_loop iterates with concurrencyLimit: 1 to execute ROLLBACK PREPARED.reverify and assert_clean re-query pg_prepared_xacts to prove transactions are gone, failing if any survived.notify_resolution sends a success card to Slack.log_clean records nominal database state.errors catches connection failures or insufficient privileges and dispatches an alert to Slack.pg_stat_activity process monitoring.xmin horizons.dry_run: true default, min_age_minutes filter, max_rollbacks safety ceiling, and an interactive Pause approval gate.pg_stat_activity shows no active long-running queries.PostgreSQL provides no automated background daemon to clean up abandoned prepared transactions; the engine assumes an external coordinator will eventually resolve them. Cron scripts running raw ROLLBACK PREPARED statements lack visibility, can abort active transactions during transient network hiccups, and offer no audit trail.
Orchestrating this with Kestra provides scheduled governance with safety gates. Findings are presented in Slack with full operational context. Destructive rollbacks require explicit human sign-off via interactive Kestra UI controls. Every executed statement, execution duration, and post-remediation verification readback is permanently logged in Kestra's execution history.
ROLLBACK PREPARED cannot execute inside a multi-statement transaction block (BEGIN ... COMMIT). The flow executes each rollback as a standalone top-level statement per loop iteration (transaction: false).min_age_minutes too low (e.g. under 10 minutes) risks rolling back a legitimate in-flight transaction during transient coordinator latency. The default 60-minute threshold protects legitimate workloads.ROLLBACK PREPARED can only be executed by a PostgreSQL superuser or by the role that initially issued PREPARE TRANSACTION. Connecting as an unprivileged application role will result in an insufficient privilege error.database = current_database(). Run this flow per database or provide distinct JDBC targets.gid) so application teams can reconcile distributed state.max_prepared_transactions > 0 (if set to 0, prepared transactions are disabled on the server).ROLLBACK PREPARED.POSTGRES_USERNAME: PostgreSQL database username.POSTGRES_PASSWORD: PostgreSQL database user password.SLACK_WEBHOOK_URL: Slack incoming webhook URL for alert and resolution notifications.| Input | Default | Purpose |
|---|---|---|
postgres_url |
jdbc:postgresql://localhost:5432/postgres |
JDBC endpoint of the target database |
use_secret_auth |
true |
When true, retrieves credentials from secrets |
postgres_username |
postgres |
Username used when use_secret_auth is false |
postgres_password |
empty | Password used when use_secret_auth is false |
min_age_minutes |
60 |
Minimum duration in minutes before a transaction is an orphan |
max_rollbacks |
20 |
Maximum number of transactions permitted to roll back in one execution |
dry_run |
true |
Must be false to execute rollbacks |
auto_remediate |
false |
Must be true to trigger operator approval and rollbacks |
outputs.detect.size: Count of orphaned prepared transactions identified.outputs.detect.rows: Candidate transaction records (gid, prepared, owner, age_minutes).outputs.summarize.values.names: Formatted summary string for notification cards.outputs.reverify.size: Remaining prepared transactions after rollback (0 on verified resolution).POSTGRES_USERNAME, POSTGRES_PASSWORD, and SLACK_WEBHOOK_URL to your Kestra namespace secrets.dry_run: true).dry_run: false and auto_remediate: true. When execution pauses at approval_gate, inspect the listed gid targets, set approved: true, and resume.assert_clean passed and Slack received the resolution confirmation.hourly_prepared_xacts_check to maintain continuous background protection.