10

Well, the question pretty much summarises it. My db activity is very update intensive, and I want to programmatically issue a Vacuum Analyze. However I get an error that says that the query cannot be executed within a transaction. Is there some other way to do it?

donatello
  • 5,727
  • 6
  • 32
  • 56
  • possible duplicate of [PostgreSQL - how to run VACUUM from code outside transaction block?](http://stackoverflow.com/questions/1017463/postgresql-how-to-run-vacuum-from-code-outside-transaction-block) – Milen A. Radev Oct 14 '10 at 10:01
  • Thanks, it is a duplicate. How can I mark it as one? – donatello Oct 17 '10 at 16:55

3 Answers3

14

This is a flaw in the Python DB-API: it starts a transaction for you. It shouldn't do that; whether and when to start a transaction should be up to the programmer. Low-level, core APIs like this shouldn't babysit the developer and do things like starting transactions behind our backs. We're big boys--we can start transactions ourself, thanks.

With psycopg2, you can disable this unfortunate behavior with an API extension: run connection.autocommit = True. There's no standard API for this, unfortunately, so you have to depend on nonstandard extensions to issue commands that must be executed outside of a transaction.

No language is without its warts, and this is one of Python's. I've been bitten by this before too.

randers
  • 5,031
  • 5
  • 37
  • 64
Glenn Maynard
  • 55,829
  • 10
  • 121
  • 131
  • 3
    just a comment that `connection.autocommit` is a boolean attribute, not a function. So to execute queries outside a transaction you can set `connection.autocommit = True` before executing the VACUUM – Manuel G Mar 22 '15 at 03:44
  • Yeah I can't agree with this more. It's ridiculous that ever after `session.commit()` we're in a new transaction. It completely goes against how `begin; ...statements; commit;` works. – kevlarr Jun 01 '20 at 18:51
12

You can turn on Postgres autocommit mode using SQLAlchemy's raw_connection (which will give you a "raw" psycopg2 connection):

import sqlalchemy
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT


engine = sqlalchemy.create_engine(url)
connection = engine.raw_connection()
connection.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cursor = connection.cursor()
cursor.execute("VACUUM ANALYSE table_name")
mdh
  • 5,355
  • 5
  • 26
  • 33
1

Not sure about older versions of SQLAlchemy, but with a recent version (1.4.x or higher) you can create an autocommit session, without handling raw connections or relying on database specific hacks:

import sqlalchemy
from sqlalchemy.orm import Session

engine = sqlalchemy.create_engine('postgresql://localhost:5432')
autocommit_engine = engine.execution_options(isolation_level="AUTOCOMMIT")
with Session(autocommit_engine) as session:
    session.execute(f'VACUUM ANALYZE public.my_table;')

The autocommit engine can be derived from any Engine object. The old Engine instance remains functional.

klamann
  • 1,697
  • 17
  • 28