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.
How it works
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:
- If orphans exist:
findings_alert alerts Slack. ceiling_check confirms count <= max_rollbacks. When auto_remediate: true and dry_run: false, approval_gate pauses for operator approval.
- Upon 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.
- If clean:
log_clean records nominal database state.
errors catches connection failures or insufficient privileges and dispatches an alert to Slack.
What you get
- Automated detection of abandoned two-phase commits invisible to standard
pg_stat_activity process monitoring.
- Protection against cluster-wide autovacuum stalls and catastrophic table bloat caused by pinned
xmin horizons.
- Multi-layered safety controls:
dry_run: true default, min_age_minutes filter, max_rollbacks safety ceiling, and an interactive Pause approval gate.
- Double-check verification readback confirming all targeted transactions were cleanly aborted.
Who it's for
- Database Administrators (DBAs) and Site Reliability Engineers managing PostgreSQL in distributed microservice architectures.
- Platform teams running distributed transaction coordinators (e.g. Narayana, Bitronix, Atomikos, Kafka Connect XA sinks, or custom two-phase commit protocols).
- Anyone troubleshooting unexplained PostgreSQL table bloat where
pg_stat_activity shows no active long-running queries.
Why orchestrate this with Kestra
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.
Pitfalls
- Transaction block restriction:
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).
- Rolling back active transactions: Two-phase commits take a few seconds during normal distributed execution. Setting
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.
- Privilege requirements:
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 scope: Prepared transactions are global to the cluster instance, but tables touched belong to specific databases. The query explicitly filters by
database = current_database(). Run this flow per database or provide distinct JDBC targets.
- Coordinator split-brain: If a distributed coordinator was partitioned and later attempts to commit a transaction that has been rolled back, the coordinator will receive an unknown transaction identifier error. The flow logs exact transaction identifiers (
gid) so application teams can reconcile distributed state.
Prerequisites
- PostgreSQL 12 or higher with
max_prepared_transactions > 0 (if set to 0, prepared transactions are disabled on the server).
- Connecting role must have superuser permissions or match the owner of the prepared transactions to execute
ROLLBACK PREPARED.
- Network connectivity from Kestra to the PostgreSQL JDBC endpoint.
- Slack incoming webhook endpoint for notifications.
Secrets
POSTGRES_USERNAME: PostgreSQL database username.
POSTGRES_PASSWORD: PostgreSQL database user password.
SLACK_WEBHOOK_URL: Slack incoming webhook URL for alert and resolution notifications.
Inputs
| 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
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).
Quick start
- Add
POSTGRES_USERNAME, POSTGRES_PASSWORD, and SLACK_WEBHOOK_URL to your Kestra namespace secrets.
- Import the flow and run a test execution with default parameters (
dry_run: true).
- Review the execution logs or Slack notification to see any detected prepared transactions, their owners, and elapsed age.
- To remediate an orphaned transaction, trigger an execution with
dry_run: false and auto_remediate: true. When execution pauses at approval_gate, inspect the listed gid targets, set approved: true, and resume.
- Verify in the execution log that
assert_clean passed and Slack received the resolution confirmation.
- Enable
hourly_prepared_xacts_check to maintain continuous background protection.
How to extend
- Route persistent findings to PagerDuty or Opsgenie if orphaned transactions exceed 24 hours without resolution.
- Add automated coordinator lookup: integrate an HTTP request task to query distributed transaction managers (e.g. Narayana or Spring Boot actuator endpoints) to check coordinator status before proposing rollback.
- Loop across multiple database endpoints in a cluster by passing an array of JDBC URLs.
Links