如何回滚所有未完成的PostgreSQL事务

Question

我该如何回滚特定数据库所有打开的Postgres事务？

我是否可以通过某种方式组合这两个语句来实现？

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

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

- Wolkenarchitekt

---

你尝试过创建一个带有循环的PL/SQL块来执行第一条语句吗？ - Ihor Romanchenko

很遗憾，如果没有使用 dblink hack 就无法工作。因为 ROLLBACK PREPARED 无法在事务中运行，而 PL/PgSQL 总是在事务中。 - Craig Ringer

我从上面的文本中推断出，您希望回滚所有已准备好的事务，而不是所有“打开”的事务。如果您实际上指的是所有打开的事务，那么这完全是另一回事了。 - Craig Ringer

Answer 1

ROLLBACK PREPARED 只影响已准备好的两阶段提交事务，对普通事务无效。

如果您真正想回滚所有“已准备事务”，那么可以循环使用 pg_prepared_xacts，就像您展示的那样。然而，由于 ROLLBACK PREPARED 不能在事务内运行，您必须从外部客户端应用程序执行它。

我只建议在不关心数据的调试/测试系统上执行此操作。否则，请在验证它们不重要后手动回滚各个事务。在大多数应用程序中，当数据很重要时，2PC 通常用于进行事务处理，而 PREPARE TRANSACTION 对于大多数应用程序来说等同于实际的 COMMIT - 他们期望提交将实际达到磁盘。当然，在这种情况下，您不应该有未提交的已准备好的 xact，因为您的 XA 事务管理器（或您正在使用的任何其他管理器）应该跟踪和恢复已准备但未提交的事务。

以下是我最近为此目的编写的一份快速而简单的脚本：

#!/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,))

- Craig Ringer

Answer 2

我曾经遇到这样的情况，无法（轻松地）使用@Craig Ringer答案中优秀的Python脚本，但是我的系统中已经安装了Groovy - 如果有人需要，以下是相同的Groovy脚本：

@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 + "'")
  }
}

- Clive Jevons

Answer 3

对于预处理事务：

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$;

- Glebka