Some commands in Postgres can’t be run inside a transaction (e.g. running
VACUUM FULL or creating indexes concurrently); they require
autocommit mode to
be enabled on the connection.
However there is a subtle gotcha when doing this as if you execute multiple statements then a transaction is implicitly created leading to an error.
conn = psycopg2.connect(...) conn.autocommit = True cur = conn.cursor() cur.execute("VACUUM FULL; SELECT 1") # exception: ActiveSqlTransaction: VACUUM cannot run inside a transaction block
I lost an hour of my life this week tracking this down until I
stumbled upon this StackOverflow answer and