How to rollback all open PostgreSQL transactions -

How do I open all postgres transactions for a specific database?

Can I do this by adding some of these 2 statements?

  - Get transaction identifier from pg_prepared_xacts to select gid where database = 'mydb'; - Rollback transaction prepared by identifier rollback 'GID';  

rollback made only two-step commit transactions Affects. It does not have any effect on the normal transaction.

If you really mean that you want to rollback all created transactions , then you will see it as although you can do with a loop on pg_prepared_xacts , because rollback is designed can not run within a transaction, you'll need an external client app.

I recommend doing this only on a debug / test system, keep in mind about the data; otherwise, after making sure to rollback the different transactions with hand they are not important 2PC usually when Data is important, and a prepaid transaction is equivalent to a real COMMIT , as far as most apps are concerned - they expect that the committed will actually be up to disk Arrived A. Of course, you know that was the case should not lose cases ready to lie because even your Aksa transaction manager (or whatever you're using) should keep track and ready-recorded transactions.

Here's a quick and dirty script that I recently wrote for such a purpose:

  #! / Usr / bin / env python # # purged all designed tasks specified database # # The easiest way to get Psycopg2 on Windows with ActiveState Python: # Active Python (http: //www.activestate. com / activepython / downloads) # psycopg2 (http://code.activestate.com/pypm/psycopg2/) import system imports psycopg2 import Upproses that LAN (sys.argv)! = 2: print ( "Usage: Clinap_ prepared_xacts.py" dbname = mydb ... '') conn = psycopg2.connect (sys.argv [1]) conn.set_isolation_level (psycopg2. Extensions.ISOLATION_LEVEL_AUTOCOMMIT) curs = conn.cursor ( ) curs.execute ( "select gid fROM pg_prepared_xacts WHERE database = current_database ()") for (gid,) in curs.fetchall (): curs.execute ( "ROLLBAC K ready% s", (gid,))