PostgreSQL - 如何在事务块外部的代码中运行VACUUM？

Question

我正在使用Python和psycopg2，尝试在每日插入数千行后运行完整的VACUUM。但问题是当我尝试在我的代码中运行VACUUM命令时，会出现以下错误：

psycopg2.InternalError: VACUUM cannot run inside a transaction block

如何在事务块外部的代码中运行它？

如果这有所区别，我有一个简单的DB抽象类，其中一部分内容如下所示（不可运行，省略异常处理和docstrings，并进行了跨行调整）：

class db(object):
    def __init__(dbname, host, port, user, password):
        self.conn = psycopg2.connect("dbname=%s host=%s port=%s \
                                      user=%s password=%s" \
                                      % (dbname, host, port, user, password))

        self.cursor = self.conn.cursor()

    def _doQuery(self, query):
        self.cursor.execute(query)
        self.conn.commit()

    def vacuum(self):
        query = "VACUUM FULL"
        self._doQuery(query)

- Wayne Koorts

---

@nosklo，好的建议，但根据Postgres文档，这与COMMIT相同。 - Wayne Koorts

你有没有使用SQLAlchemy？我曾经遇到过类似的问题，因为在SqlAlchemy中设置autocommit=True并不能实际上关闭事务。使用set_isolation_level是一种解决方法，可以访问psycopg2连接的内部方法。 - Michael Aquilina

@MichaelAquilina 我相信当时（现在已经是6年前了），这是为一个没有使用ORM的项目的一部分而做的。 - Wayne Koorts

Answer 1

经过更多的搜索，我发现了psycopg2连接对象的isolation_level属性。原来将其更改为0就可以使你退出事务块。将上述类的vacuum方法更改如下即可解决此问题。请注意，我还将隔离级别重置为以前的值，以防万一（默认值似乎为1）。

def vacuum(self):
    old_isolation_level = self.conn.isolation_level
    self.conn.set_isolation_level(0)
    query = "VACUUM FULL"
    self._doQuery(query)
    self.conn.set_isolation_level(old_isolation_level)

这篇文章（在该页面的最后部分）简要介绍了此上下文中的隔离级别。

- Wayne Koorts

---

或者，避免使用神秘数字：self.conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)的意思是设置PostgreSQL连接的事务隔离级别为自动提交。 - Nuno André

我曾经在许多网站上漫游，试图找到解决方案。这个方法就像魔法一样奏效了。谢谢@wayne。 - Inaam Ilahi

@InaamIlahi 很高兴这些年后它仍然能对某人有所帮助！ :) - Wayne Koorts

Answer 2

如果您已经尝试了所有与此问题相关的建议但仍然没有成功，那么您可能和我一样遭受同样的命运：在一个execute()调用中有两个或更多个SQL语句。结果发现，Postgres会在第一个语句执行后（由;分隔）重置任何自动提交/隔离。我最终在这里找到了解决方案：https://github.com/psycopg/psycopg2/issues/1201 因此，不要做这样的事情：

cursor.execute("SELECT 1; VACUUM FULL")

请改为：

cursor.execute("SELECT 1")
cursor.execute("VACUUM FULL")

- tombh

Answer 3

此外，您还可以使用以下方式获得由Vacuum或Analyse提供的消息：

>> print conn.notices #conn is the connection object

这个命令会打印一个查询日志信息列表，类似于Vacuum和Analyse:

INFO:  "usuario": processados 1 de 1 páginas, contendo 7 registros vigentes e 0 registros não vigentes; 7 registros amostrados, 7 registros totais estimados   
INFO:  analisando "public.usuario"

这对数据库管理员可能很有用 ^^

- Diego Guimaraes

---

你需要运行 cursor.execute('VACUUM FULL VERBOSE') 命令才能在该属性中实际获取到内容。 - Vlax

Answer 4

尽管在当前版本的PostgreSQL中，执行完整的Vacuum可能存在问题，但在某些大规模操作之后，强制执行“Vacuum Analyze”或“Reindex”可以提高性能或清理磁盘使用情况。这是针对PostgreSQL特定的，需要进行清理以适合其他数据库。

from django.db import connection
# Much of the proxy is not defined until this is done
force_proxy = connection.cursor()
realconn=connection.connection
old_isolation_level = realconn.isolation_level
realconn.set_isolation_level(0)
cursor = realconn.cursor()
cursor.execute('VACUUM ANALYZE')
realconn.set_isolation_level(old_isolation_level)

不幸的是，Django提供的连接代理无法访问set_isolation_level。

- Chris Dukes

---

force_proxy = connection.cursor() 是什么意思？你没有使用那个变量。 - Cerin

Answer 5

请注意，如果您正在使用Django和South进行迁移，您可以使用以下代码执行VACUUM ANALYZE。

def forwards(self, orm):

    db.commit_transaction()
    db.execute("VACUUM ANALYZE <table>")

    #Optionally start another transaction to do some more work...
    db.start_transaction()

- donturner

Answer 6

虽然这不是OP的确切情况，但我也遇到了这个问题。事实证明，当连接被用作上下文时，它与隐式事务有关。例如，即使使用了正确的隔离级别，如果OP的代码类似于以下内容，由于不同的原因，它仍将遇到完全相同的错误：

class db(object):
    def __init__(dbname, host, port, user, password):
        self.conn = psycopg2.connect("dbname=%s host=%s port=%s \
                                      user=%s password=%s" \
                                      % (dbname, host, port, user, password))
        self.conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)

    def _doQuery(self, query):
        # THE FOLLOWING LINE OPENS AN IMPLICIT TRANSACTION
        # No matter the autocommit settings, this WILL create a transaction
        with self.conn:
            with conn.cursor() as cursor:
                cursor.execute(query)
                self.conn.commit()

    def vacuum(self):
        query = "VACUUM FULL"
        self._doQuery(query)

解决方案就是不要将连接作为上下文使用：

    def _doQuery(self, query):
        # Note no "with conn:" context anymore
        with conn.cursor() as cursor:
            cursor.execute(query)
            self.conn.commit()

- bsplosion

Answer 7

我不熟悉psycopg2和PostgreSQL，只了解apsw和SQLite，所以我想我无法提供“psycopg2”的帮助。

但是我认为，PostgreSQL可能与SQLite类似，它有两种操作模式：

• 在事务块之外：这在语义上等同于在每个单独的SQL操作周围有一个事务块
• 在由“BEGIN TRANSACTION”标记并由“END TRANSACTION”结束的事务块内

如果是这种情况，则问题可能出现在访问层psycopg2中。当它通常以隐式插入事务的方式运行时，直到进行提交时，可能没有“标准方法”来进行清理。

当然，可能存在“psycopg2”的特殊“vacuum”方法或特殊操作模式，其中不会启动隐式事务。

如果没有这样的可能性，那么只剩下一种选择（而不改变访问层;-)）：

大多数数据库都有一个 shell 程序来访问数据库。程序可以通过管道运行此 shell 程序（将 vacuum 命令输入 shell），从而使用 shell 程序进行清理。由于清理本身是一个缓慢的操作，因此启动外部程序的时间可以忽略不计。当然，实际程序应该在执行之前提交所有未提交的工作，否则可能会出现死锁情况 - 清理必须等待您最后一次事务结束。

- Juergen

---

感谢您详细的回答。事实证明，解决方案与“隔离级别”有关，请参见下面的答案。 - Wayne Koorts

Answer 8

不要这样做 - 你不需要 VACUUM FULL。实际上，如果你运行的是相当新的 Postgres 版本（比如 >8.1），你甚至不需要手动运行普通的 VACUUM。

- Milen A. Radev

---

根据您的使用情况，我认为仍然有时需要手动吸尘。 - rfusca

有一些，但现在没有那么多了。而且绝对不应该使用VACUUM FULL。 - Magnus Hagander

我正在学习PostGres，并且处理一些大型表格。所有的书籍（从8.*或9.*的角度来看）都提到在大量更新之后手动运行VACUUM ANALYZE，或者使用守护进程自动运行。 - winwaed

在每天插入数千行的操作之后，这样的实用程序在完成后肯定应该进行VACUUM。 - Brad Koch

@Brad Koch：VACUUM 和 VACUUM FULL 之间有显著的区别。 - Milen A. Radev

巨大的差异，但是在大批量更新之后，无论是由您触发还是自动清理，您都需要进行VACUUM ANALYZE。仅仅运行纯粹的手动VACUUM是有点误导性的。 - Brad Koch