Can I release "VACUUM ANALYZE <tablename>" from psycopg2 or sqlalchemy for PostgreSQL?

Question

Well, the question pretty much sums it up. The activity of my db is being updated very intensively, and I want to release Vacuum Analyze programmatically. However, I get an error message that says the request could not be executed in a transaction. Is there any other way to do this?

Answer 1

This is a flaw in the Python DB-API: it starts the transaction for you. He should not do this; whether and when to start a transaction should be up to the programmer. Low-level, basic APIs like this do not have to babysit the developer and do things like launch transactions behind our backs. We are big boys - we can start the transactions ourselves, thanks.

With psycopg2, you can disable this unsuccessful behavior with the API extension: call connection.autocommit() . Unfortunately, there is no standard API for this, so you must depend on non-standard extensions to issue commands that must be executed outside the transaction.

There is no language without warts, and this is one of Python. I was also bitten by this.

Answer 2

You can enable Postgres autocommit mode using SQLAlchemy 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")