On , I learnt ...

About a gotcha with psycopg2’s autocommit handling

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 related psycopg2 issue.