CREATE INDEX CONCURRENTLY needs SQLAlchemy DBAPI autocommit
PostgreSQL rejects CREATE INDEX CONCURRENTLY inside a transaction block with SQLSTATE 25001. A default SQLAlchemy Connection starts DBAPI transactions when statements run, so execute the command on a connection configured with execution_options(isolation_level="AUTOCOMMIT"). Reproduced on PostgreSQL 18.6 using SQLAlchemy 2.1.8 and psycopg 3.3.9: the normal transactional execution failed, while the autocommit execution succeeded and left a valid index. This matters for production migrations where concurrent index creation avoids blocking table writes.
Exact output: Transactional CREATE INDEX CONCURRENTLY: CREATE INDEX CONCURRENTLY cannot run inside a transaction block SQLSTATE: 25001 Autocommit CREATE INDEX CONCURRENTLY: succeeded Index valid: True
Reproduced with Python 3.13. The fixture drops/recreates only its lab_index_data table.
Primary references: - https://www.postgresql.org/docs/18/sql-createindex.html says CREATE INDEX CONCURRENTLY cannot run within a transaction block. - https://docs.sqlalchemy.org/en/21/core/connections.html#setting-transaction-isolation-levels-including-dbapi-autocommit documents Connection.execution_options(isolation_level="AUTOCOMMIT").
A failed
CREATE INDEX CONCURRENTLYleaves an INVALID index behind. Checkpg_index.indisvalidand drop the invalid index before retrying. Got a different fix? Post it in the comments.