PostgreSQL - 如何从事务块外的代码运行 VACUUM？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1017463/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL - how to run VACUUM from code outside transaction block?
提问by Wayne Koorts
I am using Python with psycopg2 and I'm trying to run a full VACUUMafter a daily operation which inserts several thousand rows. The problem is that when I try to run the VACUUMcommand within my code I get the following error:
我正在将 Python 与 psycopg2 一起使用，并且我正在尝试VACUUM在插入数千行的日常操作之后运行完整。问题是，当我尝试VACUUM在我的代码中运行命令时，出现以下错误：
psycopg2.InternalError: VACUUM cannot run inside a transaction block
How do I run this from the code outside a transaction block?
如何从事务块外的代码运行它？
If it makes a difference, I have a simple DB abstraction class, a subset of which is displayed below for context (not runnable, exception-handling and docstrings omitted and line spanning adjustments made):
如果它有所不同，我有一个简单的 DB 抽象类，它的一个子集显示在下面的上下文中（不可运行，省略了异常处理和文档字符串，并进行了行跨度调整）：
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)
回答by Wayne Koorts
After more searching I have discovered the isolation_level property of the psycopg2 connection object. It turns out that changing this to 0will move you out of a transaction block. Changing the vacuum method of the above class to the following solves it. Note that I also set the isolation level back to what it previously was just in case (seems to be 1by default).
经过更多搜索，我发现了 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)
This article(near the end on that page) provides a brief explanation of isolation levels in this context.
这篇文章（接近该页面的结尾）在此上下文中提供了对隔离级别的简要说明。
回答by Diego Guimaraes
Additionally, you can also get the messages given by the Vacuum or Analyse using:
此外，您还可以使用以下方法获取 Vacuum 或 Analyze 给出的消息：
>> print conn.notices #conn is the connection object
this command print a list with the log message of queries like Vacuum and Analyse:
此命令打印一个列表，其中包含诸如 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"
This can be useful to the DBAs ^^
这对 DBA 很有用 ^^
回答by Chris Dukes
While vacuum full is questionable in current versions of postgresql, forcing a 'vacuum analyze' or 'reindex' after certain massive actions can improve performance, or clean up disk usage. This is postgresql specific, and needs to be cleaned up to do the right thing for other databases.
虽然在当前版本的 postgresql 中真空已满是有问题的，但在某些大规模操作后强制执行“真空分析”或“重新索引”可以提高性能或清理磁盘使用情况。这是 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)
Unfortunately the connection proxy provided by django doesn't provide access to set_isolation_level.
不幸的是，django 提供的连接代理不提供对 set_isolation_level 的访问。
回答by donturner
Note if you're using Django with South to perform a migration you can use the following code to execute a VACUUM ANALYZE.
请注意，如果您将 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()
回答by Juergen
I don't know psycopg2 and PostgreSQL, but only apsw and SQLite, so I think I can not give a "psycopg2" help.
我不知道 psycopg2 和 PostgreSQL，但只知道 apsw 和 SQLite，所以我想我无法提供“psycopg2”帮助。
But it seams to me, that PostgreSQL might work similar as SQLite does, it has two modes of operation:
但在我看来，PostgreSQL 的工作方式可能与 SQLite 类似，它有两种操作模式：
- Outside a transaction block: This is semantically equivalent to have a transaction block around every single SQL operation
- Inside a transaction block, that is marked by "BEGIN TRANSACTION" and ended by "END TRANSACTION"
- 在事务块之外：这在语义上等同于在每个 SQL 操作周围都有一个事务块
- 在交易块内，由“BEGIN TRANSACTION”标记并以“END TRANSACTION”结束
When this is the case, the problem could be inside the access layer psycopg2. When it does normally operate in a way that transactions are implicitely inserted until a commit is made, there could be no "standard way" to make a vacuum.
在这种情况下，问题可能出在访问层 psycopg2 内部。当它通常以隐式插入事务的方式运行时，直到进行提交，就没有“标准方式”来创建真空。
Of course it could be possible, that "psycopg2" has its special "vacuum" method, or a special operation mode, where no implicit transactions are started.
当然，“psycopg2”有其特殊的“真空”方法，或特殊的操作模式，其中没有启动隐式事务，这也是可能的。
When no such possibilities exists, there stays one single option (without changing the access layer ;-) ):
如果不存在这种可能性，则只有一个选项（不更改访问层 ;-) ）：
Most databases have a shell programm to access the database. The program could run this shell program with a pipe (entering the vacuum-command into the shell), thus using the shell programm to make the vacuum. Since vacuum is a slow operation as such, the start of an external programm will be neglectible. Of course, the actual program should commit all uncommited work before, else there could be a dead-lock situation - the vacuum must wait until end of your last transaction.
大多数数据库都有一个 shell 程序来访问数据库。程序可以通过管道运行这个shell程序（在shell中输入vacuum-command），从而使用shell程序来制作真空。由于真空本身是一个缓慢的操作，外部程序的启动将被忽略。当然，实际的程序应该先提交所有未提交的工作，否则可能会出现死锁情况——真空必须等到最后一个事务结束。
回答by Milen A. Radev
Don't do it - you don't need VACUUM FULL. Actually if you run somewhat recent version of Postgres (let's say > 8.1) you don't even need to run plain VACUUM manually.
不要这样做 - 你不需要 VACUUM FULL。实际上，如果您运行较新版本的 Postgres（比方说 > 8.1），您甚至不需要手动运行普通的 VACUUM。
