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';
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,))
Was just in the situation whereby I didn't have the option of (easily) using the excellent Python script in @Craig Ringer's answer above, but had Groovy set up on my system - here's the same thing in Groovy, just in case it can be of use to anyone else:
@GrabConfig(systemClassLoader=true)
@Grab(group='org.postgresql', module='postgresql', version='9.4.1212')
import groovy.sql.*
Sql.withInstance('jdbc:postgresql://pg-host-here/your-db-here', 'username', 'password', 'org.postgresql.Driver') { sql ->
sql.eachRow('select gid from pg_prepared_xacts where database = current_database()') { row ->
println "TX GID: ${row.gid}"
sql.execute("rollback prepared '" + row.gid + "'")
}
}
For prepared transactions:
DO
$do$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT database, gid FROM pg_prepared_xacts
LOOP
PERFORM dblink_exec(format('dbname=%s', r.database), format($cmd$ROLLBACK PREPARED '%s'$cmd$, r.gid));
END LOOP;
END;
$do$;
ROLLBACK PREPAREDcan't run in a transaction and PL/PgSQL is always in a transaction.