How to rollback all open PostgreSQL transactions

Question

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

Can I do it by combining these 2 statements somehow?

-- get transaction identifiers
SELECT gid FROM pg_prepared_xacts WHERE database='mydb';

-- rollback transaction by identifier
ROLLBACK PREPARED 'GID';

Answer 1

ROLLBACK PREPARED only affects prepared two-phase commit transactions. It has no effect on ordinary transactions.

If you actually mean that you wish to rollback all prepared transactions then you can do it with a loop over pg_prepared_xacts as you've shown. However, because ROLLBACK PREPARED cannot run within a transaction, you must do it from an external client app.

I only recommend doing this on a debug/test system where you don't care about the data. Otherwise, rollback individual transactions by hand after verifying that they're not important. 2PC is generally used when the data is important, and a PREPARE TRANSACTION is equivalent to an actual COMMIT as far as most apps are concerned - they expect that the commit will in fact reach disk. Of course, you shouldn't have lost prepared xacts lying around in that case because your XA transaction manager (or whatever you're using) should keep track of and recover prepared-but-not-committed transactions.

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

#!/usr/bin/env python
#
# Purges all prepared xacts from the specified database
#
# On Windows the easiest way to get psycopg2 is with ActiveState python:
#
# ActivePython (http://www.activestate.com/activepython/downloads)
# psycopg2 (http://code.activestate.com/pypm/psycopg2/)

import sys
import psycopg2
import subprocess

if len(sys.argv) != 2:
    print('Usage: cleanup_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("ROLLBACK PREPARED %s", (gid,))