Index with postgresql_concurrently=True fails to create

[Andrew Wason]

Creating an index with postgresql_concurrently=True fails to create because it is created in a transaction which is not supported. It raises an exception "CREATE INDEX CONCURRENTLY cannot run inside a transaction block". I attempted to create the index in various ways with transactions disabled but could not find a way.

sqlalchemy 1.1.4 psycopg2 2.6.2 PostgreSQL 9.6.1 python 2.7.6

>>> import sqlalchemy as sa
>>> engine = sa.create_engine("postgresql://cureatr@localhost/cureatr_mongodb?sslmode=require")
>>> metadata = sa.MetaData()
>>> table = sa.Table("foo", metadata, sa.Column("id", sa.String))
>>> table.create(engine)
>>> index = sa.Index("foo_idx", table.c.id, postgresql_concurrently=True)
>>> index.create(engine)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 3362, in create
    bind._run_visitor(ddl.SchemaGenerator, self)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1920, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1529, in _run_visitor
    **kwargs).traverse_single(element)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 791, in visit_index
    self.connection.execute(CreateIndex(index))
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1002, in _execute_ddl
    compiled
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1393, in _handle_dbapi_exception
    exc_info
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 469, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.InternalError: (psycopg2.InternalError) CREATE INDEX CONCURRENTLY cannot run inside a transaction block
 [SQL: 'CREATE INDEX CONCURRENTLY foo_idx ON foo (id)']
>>> with engine.connect().execution_options(autocommit=True) as conn:
...     index.create(conn)
...
Traceback (most recent call last):
  File "<stdin>", line 2, in <module>
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 3362, in create
    bind._run_visitor(ddl.SchemaGenerator, self)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1529, in _run_visitor
    **kwargs).traverse_single(element)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 791, in visit_index
    self.connection.execute(CreateIndex(index))
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1002, in _execute_ddl
    compiled
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1393, in _handle_dbapi_exception
    exc_info
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 469, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.InternalError: (psycopg2.InternalError) CREATE INDEX CONCURRENTLY cannot run inside a transaction block
 [SQL: 'CREATE INDEX CONCURRENTLY foo_idx ON foo (id)']
>>> engine.execute(sa.schema.CreateIndex(index).execution_options(autocommit=False))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2055, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1002, in _execute_ddl
    compiled
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1393, in _handle_dbapi_exception
    exc_info
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/virtualenv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 469, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.InternalError: (psycopg2.InternalError) CREATE INDEX CONCURRENTLY cannot run inside a transaction block
 [SQL: 'CREATE INDEX CONCURRENTLY foo_idx ON foo (id)']

[Mike Bayer]

you need to set psycopg2 AUTOCOMMIT so there's no transaction block:

import sqlalchemy as sa
engine = sa.create_engine(
    "postgresql://scott:tiger@localhost/test")
metadata = sa.MetaData()
table = sa.Table("foo", metadata, sa.Column("id", sa.String))
table.create(engine)
index = sa.Index("foo_idx", table.c.id, postgresql_concurrently=True)

with engine.connect() as conn:
    with conn.execution_options(isolation_level='AUTOCOMMIT'):
        index.create(conn)

docs are missing this so will add.

[Mike Bayer]

• document how to use autocommit isolation level for CONCURRENTLY, fixes #3887

Change-Id: I6d1a13b7bb4169204105c7a100d17cfed3ded9d1

→ <<cset 0460bc79d998>>

[Mike Bayer]

• document how to use autocommit isolation level for CONCURRENTLY, fixes #3887

Change-Id: I6d1a13b7bb4169204105c7a100d17cfed3ded9d1 (cherry picked from commit 0460bc79d9986132646049d8167bd5dbe3388a65)

→ <<cset b0c4a6e1bc7a>>