batch fails on tables that have indexes

[sqlalchemy-bot]

Migrated issue, originally created by Michael Bayer (@zzzeek)

not sure how nobody has come across this yet, it is pretty basic:

diff --git a/tests/test_batch.py b/tests/test_batch.py
index 0f0aada..79e558d 100644
--- a/tests/test_batch.py
+++ b/tests/test_batch.py
@@ -860,6 +860,17 @@ class BatchRoundTripTest(TestBase):
         )
         return nopk
 
+    def _table_w_index_fixture(self):
+        t = Table(
+            't_w_ix', self.metadata,
+            Column('id', Integer, primary_key=True),
+            Column('thing', Integer),
+            Column('data', String),
+        )
+        Index('ix_thing', t.c.thing)
+        t.create(self.conn)
+        return t
+
     def tearDown(self):
         self.metadata.drop_all(self.conn)
         self.conn.close()
@@ -871,6 +882,11 @@ class BatchRoundTripTest(TestBase):
             data
         )
 
+    def test_ix_existing(self):
+        self._table_w_index_fixture()
+        with self.op.batch_alter_table("t_w_ix") as batch_op:
+            batch_op.alter_column('data', type_=Integer)
+
     def test_fk_points_to_me_auto(self):
         self._test_fk_points_to_me("auto")
 

SQLite holds the index name as unique across the whole DB, so we get:

OperationalError: (sqlite3.OperationalError) index ix_thing already exists [SQL: u'CREATE INDEX ix_thing ON _alembic_batch_temp (thing)']

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

• Fixed bug in batch mode where a table that had pre-existing indexes
  would create the same index on the new table with the same name,
  which on SQLite produces a naming conflict as index names are in a
  global namespace on that backend. Batch mode now defers the production
  of both existing and new indexes until after the entire table transfer
  operation is complete, which also means those indexes no longer take
  effect during the INSERT from SELECT section as well; the indexes
  are applied in a single step afterwards.
  fixes batch fails on tables that have indexes #333

→ 0110166

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed