PostgreSQL Transaction Resolution Unknown (SQLSTATE 08007)

When a two-phase commit (2PC) connection drops after PREPARE TRANSACTION but before the outcome of COMMIT PREPARED or ROLLBACK PREPARED is confirmed, PostgreSQL raises SQLSTATE 08007 with condition name transaction_resolution_unknown. The client receives a message such as ERROR: could not connect to the server or the driver surfaces it as an 08007 state, indicating that the fate of the prepared transaction is unknown.

What This Error Means

SQLSTATE class 08 covers connection exception errors — situations where the communication link between client and server was lost or could not be established at a critical moment. The 08007 subclass is specific to two-phase commit (2PC): the connection failed during or immediately after the second phase, leaving the transaction manager unable to confirm whether the transaction was ultimately committed or rolled back on the PostgreSQL server.

Two-phase commit splits a transaction into two steps. PREPARE TRANSACTION 'txn_id' durably writes the prepared transaction to disk and returns control to the transaction coordinator. The coordinator then issues COMMIT PREPARED 'txn_id' or ROLLBACK PREPARED 'txn_id' to finalize the outcome. If the network drops between these two steps — or if the coordinator itself crashes — the client never receives acknowledgment of the final state, resulting in transaction_resolution_unknown.

The prepared transaction itself is not lost. PostgreSQL stores it durably in pg_prepared_xacts and holds all its locks until an explicit COMMIT PREPARED or ROLLBACK PREPARED is issued. The uncertainty is purely on the coordinator side: the coordinator does not know whether its final command reached and was executed by PostgreSQL before the connection was severed.

Common Causes

1. Network interruption between the second-phase command and its acknowledgment. The coordinator sends COMMIT PREPARED but the TCP connection drops before PostgreSQL returns the success response. The transaction may or may not have committed.

2. Coordinator crash during the second phase. A distributed transaction manager (XA-compliant JDBC driver, application-level saga coordinator, or middleware such as Atomikos or Narayana) crashes after sending the final command but before recording the outcome locally.

3. PostgreSQL server restart or failover during commit. If the server undergoes an unplanned restart between receiving COMMIT PREPARED and sending the confirmation, the coordinator sees a broken connection and cannot determine whether PostgreSQL applied the commit before or after the crash.

4. Aggressive connection-pool or proxy timeouts. A proxy such as PgBouncer or HAProxy kills connections that appear idle; if the second-phase command is treated as idle time mid-flight, the connection is closed and the coordinator receives 08007.

How to Fix transaction_resolution_unknown

1. Query pg_prepared_xacts to find the transaction's actual state. Immediately after receiving 08007, connect to PostgreSQL and check whether the prepared transaction still exists:

   SELECT gid, prepared, owner, database
   FROM pg_prepared_xacts
   WHERE gid = 'your_transaction_id';
   

   • If the row exists, the transaction is still in the prepared state — it has neither committed nor rolled back. You must resolve it manually (see step 2).
   • If the row does not exist, the transaction either committed or rolled back before the connection dropped. Check application data or sequence values to determine which outcome occurred, then proceed accordingly.

2. Manually commit or roll back the prepared transaction. Once you have determined the correct outcome (from application logic, a distributed log, or the coordinator's own recovery log), issue the appropriate command as a superuser or the transaction owner:

   -- If the transaction should have committed:
   COMMIT PREPARED 'your_transaction_id';
   
   -- If the transaction should have rolled back:
   ROLLBACK PREPARED 'your_transaction_id';
   

3. Implement coordinator-side recovery logging. The coordinator must durably record its intent (commit or rollback) before sending the second-phase command to PostgreSQL. On restart, the coordinator can replay its log and re-issue the outstanding COMMIT PREPARED or ROLLBACK PREPARED for any unresolved GIDs.

4. Monitor and alert on stale prepared transactions. Long-lived entries in pg_prepared_xacts hold row-level locks and prevent vacuum from reclaiming dead tuples, which can cause table bloat. Set up a periodic check:

   SELECT gid, prepared, NOW() - prepared AS age
   FROM pg_prepared_xacts
   WHERE prepared < NOW() - INTERVAL '5 minutes'
   ORDER BY prepared;
   

   Alert if any prepared transaction exceeds your expected coordinator recovery time.

5. Review proxy and firewall timeout settings. Ensure that connection proxies do not forcibly close connections that are actively mid-transaction. Increase idle-in-transaction timeouts on the proxy side, or configure keepalives so that the proxy can distinguish active from truly idle connections.

Additional Information

• max_prepared_transactions must be set to a value greater than zero in postgresql.conf to allow PREPARE TRANSACTION at all. The default is 0 (two-phase commit disabled). If 2PC is not intentionally used by your application, verify that no middleware is enabling it unexpectedly.
• Related SQLSTATE codes in class 08: 08000 (connection exception), 08003 (connection does not exist), 08006 (connection failure — same class, but raised when the connection itself fails rather than at transaction resolution time).
• XA drivers (Java/JTA, .NET System.Transactions, and some ORM frameworks) automatically use two-phase commit when enlisted in a distributed transaction. These drivers surface 08007 through their own exception hierarchies — for example, javax.transaction.xa.XAException with error code XA_HEURCOM or XA_HEURRB when the outcome is known heuristically, versus a generic rollback exception when it is not.
• Unresolved prepared transactions block autovacuum from advancing the transaction ID horizon, which can eventually trigger transaction ID wraparound warnings. Treat any prepared transaction older than a few minutes as a high-priority operational issue.
• PostgreSQL does not automatically time out or roll back orphaned prepared transactions. They persist indefinitely until manually resolved.

Frequently Asked Questions

Will PostgreSQL automatically clean up an unresolved prepared transaction? No. PostgreSQL holds the prepared transaction indefinitely until an explicit COMMIT PREPARED or ROLLBACK PREPARED is issued. There is no built-in timeout. This is by design — the durability guarantee of two-phase commit requires that the prepared state survive server restarts. It is the coordinator's responsibility to resolve all prepared transactions.

Can I safely roll back a prepared transaction if I'm unsure? Only if your application can tolerate the rollback. If the coordinator's intent was to commit and the commit would be visible to other systems (e.g., an external service was already notified), rolling back creates an inconsistency. Consult your coordinator's recovery log to determine intent before issuing either command.

Does 08007 mean data was lost or corrupted? No. The prepared transaction is durable — its changes are on disk and its locks are held. The uncertainty is only about the final outcome, not about the safety of the data. Once you resolve the transaction (commit or rollback), the database returns to a consistent state.

Why do I see 08007 from my Java/JTA application but not when using psql directly? Java XA drivers and JTA transaction managers explicitly use two-phase commit when coordinating distributed transactions. Plain psql sessions use single-phase commit by default and never issue PREPARE TRANSACTION unless you do so explicitly. If your Java application is triggering 08007, check whether your JTA/XA datasource configuration is correct and whether the transaction manager's recovery mechanism is running.