2

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?

  • Wouldn't that destroy the whole point of using a prepared transaction? – jjanes May 20 '14 at 15:57
  • 1
    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
2

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')]
    conn.execute("rollback;")
    for t in trs:
        conn.rollback_prepared(t)
    conn.close()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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