I am using Python with psycopg2 and I'm trying to run a full VACUUM
after a daily operation which inserts several thousand rows. The problem is that when I try to run the VACUUM
command within my code I get the following error:
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):
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)
After more searching I have discovered the isolation_level property of the psycopg2 connection object. It turns out that changing this to 0
will 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 1
by default).
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.