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.