PostgreSQL - how to run VACUUM from code outside transaction block?
For anyone else that has tried all the suggestions around this problem without success, you may be suffering the same fate as me: I had 2 (or more) SQL statements in one execute()
call. It turns out that Postgres itself resets any autocommit/isolation after the first statement (separated by a ;
). I finally came across the solution here: https://github.com/psycopg/psycopg2/issues/1201
So don't do something like this:
cursor.execute("SELECT 1; VACUUM FULL")
Instead do:
cursor.execute("SELECT 1")
cursor.execute("VACUUM FULL")
Additionally, you can also get the messages given by the Vacuum or Analyse using:
>> print conn.notices #conn is the connection object
this command print a list with the log message of queries like Vacuum and 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 ^^
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.