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?
Advertisement
Answer
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.