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?

3
  • Wouldn't that destroy the whole point of using a prepared transaction?
    – jjanes
    May 20, 2014 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, 2014 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, 2015 at 3:18

1 Answer 1

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 and acknowledge you have read our privacy policy.

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