Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm using two-phase commit in Postgres to reliably work with an external system (Amazon EC2).

I.e. I first do a PREPARE call, then call the external system. If the external system call fails for some reason, I simply rollback the prepared transaction.

It works fine. However, if I terminate my application while a distributed transaction is open, it will hang forever. Is it possible to automatically rollback 2PC transactions on session disconnection?

share|improve this question
Wouldn't that destroy the whole point of using a prepared transaction? – jjanes May 20 '14 at 15:57
Not in my particular use-case. If I terminate my application then the external call is also going to be terminated. It's not possible to recover it later. – Cyberax May 21 '14 at 16:07
I think what you are doing is completely equivalent to leaving the transaction open while calling the external service. – Gavin Wahl Sep 11 '15 at 3:18

Anyway, it seems impossible after reading PostgreSQL source code. I settled for rolling back all the prepared transactions during application initialization.

Python code is:

def cleanup_prepared_xas(engine):
    conn = engine.connect()
    trs = [t[0] for t in conn.execute('select gid from pg_prepared_xacts')]
    for t in trs:
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.