Alembic: Postgres CREATE INDEX CONCURRENTLY

[sqlalchemy-bot]

Migrated issue, originally created by Iuri de Silvio (@iurisilvio)

Is it possible to use the CONCURRENTLY statement to create my Postgres index?

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

easiest way right now is just emit op.execute("CREATE INDEX ... CONCURRENTLY ...")

For a feature add here please submit a pull request to SQLAlchemy, providing "postgresql_concurrently" as an option to the Index construct..thanks!

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

sqlalchemy side feature

[sqlalchemy-bot]

Iuri de Silvio (@iurisilvio) wrote:

Thanks! I sent the pull request to sqlalchemy.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

are you setting the psycopg2 connection into autocommit mode to use this? per https://www.postgresql.org/docs/9.1/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY "nother difference is that a regular CREATE INDEX command can be performed within a transaction block, but CREATE INDEX CONCURRENTLY cannot."

[sqlalchemy-bot]

Iuri de Silvio (@iurisilvio) wrote:

This flag is useful only to migrate with alembic, so it is not hard to workaround the transaction there.

Works for me in production without any downtime or postgres locking. Example:

def upgrade():
    op.execute('COMMIT')
    op.create_index('ix_1', 't1', ['col1'], postgresql_concurrently=True)
    op.create_index('ix_2', 't1', ['col2'], postgresql_concurrently=True)

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

so we need the **kw on op.drop_index() also to pass through....

[sqlalchemy-bot]

Iuri de Silvio (@iurisilvio) wrote:

Sure! I just sent the PR to sqlalchemy, didn't tested it with alembic yet.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed