Renaming col with index in batch op fails

[adrien-berchet]

Describe the bug
Renaming a column with an index when using batch operation with recreate="always" fails.

Expected behavior
The column should be renamed and then the index should be created using the new column name. For it seems that it tries to create the index using the old column name.

To Reproduce
Please try to provide a Minimal, Complete, and Verifiable example, with the migration script and/or the SQLAlchemy tables or models involved.
See also Reporting Bugs on the website.

from alembic.migration import MigrationContext
from alembic.operations import Operations
from sqlalchemy import MetaData
from sqlalchemy import Table
from sqlalchemy import Integer
from sqlalchemy import Column
from sqlalchemy import Index
from sqlalchemy import create_engine

engine = create_engine("YOUR_DB_URL", echo=True)
conn = engine.connect()

t = Table(
    'the_table',
    MetaData(),
    Column("id", Integer, primary_key=True),
    Column("int_col", Integer),
    Index("idx_int_col", "int_col"),
)
t.drop(bind=conn, checkfirst=True)
t.create(bind=conn)

mc = MigrationContext.configure(conn)
op = Operations(mc)

with op.batch_alter_table("the_table", recreate="always") as batch_op:
    batch_op.alter_column("int_col", new_column_name="new_int_col")

Error

Traceback (most recent call last):
  File "/home/adrien/.virtualenvs/geoalchemy/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/home/adrien/.virtualenvs/geoalchemy/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedColumn: column "int_col" does not exist


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "tests/toto.py", line 29, in <module>
    batch_op.alter_column("int_col", new_column_name="new_int_col")
  File "/usr/lib/python3.8/contextlib.py", line 120, in __exit__
    next(self.gen)
  File "/mnt/Data/Work/ForCity/alembic/alembic/operations/base.py", line 374, in batch_alter_table
    impl.flush()
  File "/mnt/Data/Work/ForCity/alembic/alembic/operations/batch.py", line 146, in flush
    batch_impl._create(self.impl)
  File "/mnt/Data/Work/ForCity/alembic/alembic/operations/batch.py", line 447, in _create
    op_impl.create_index(idx)
  File "/mnt/Data/Work/ForCity/alembic/alembic/ddl/postgresql.py", line 82, in create_index
    self._exec(CreateIndex(index))
  File "/mnt/Data/Work/ForCity/alembic/alembic/ddl/impl.py", line 196, in _exec
    return conn.execute(construct, multiparams)
  File "/home/adrien/.virtualenvs/geoalchemy/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1306, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/home/adrien/.virtualenvs/geoalchemy/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
    return connection._execute_ddl(
  File "/home/adrien/.virtualenvs/geoalchemy/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1398, in _execute_ddl
    ret = self._execute_context(
  File "/home/adrien/.virtualenvs/geoalchemy/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/home/adrien/.virtualenvs/geoalchemy/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/home/adrien/.virtualenvs/geoalchemy/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/adrien/.virtualenvs/geoalchemy/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/home/adrien/.virtualenvs/geoalchemy/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column "int_col" does not exist

[SQL: CREATE INDEX idx_int_col ON the_table (int_col)]
(Background on this error at: https://sqlalche.me/e/14/f405)

Versions.

• OS: Linux Mint 20.3
• Python: 3.8.10
• Alembic: 1.7.7
• SQLAlchemy: 1.4.36
• Database: 12.10
• DBAPI: ?

Additional context
I was trying to understand how the batch operations work, especially when Alembic creates a temporary table (which is required for SQLite), and I found this case that looks like a bug. But I am quite new to Alembic so maybe I don't use it correctly?

[zzzeek]

havent looked yet but batch mode is very poorly supported for databases that are not SQLite. if you're just trying to get something done I'd keep it at "sqlite only" for now.

[adrien-berchet]

My original issue comes from a use case using SQLite but I ran the test with postgresql out of habit, sorry for the mix up.
Running the same code on a SQLite DB gives the same error, here is the full stack trace:

Traceback (most recent call last):
  File "/home/adrien/.virtualenvs/geoalchemy/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/home/adrien/.virtualenvs/geoalchemy/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such column: int_col

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "tests/toto.py", line 27, in <module>
    batch_op.alter_column("int_col", new_column_name="new_int_col")
  File "/usr/lib/python3.8/contextlib.py", line 120, in __exit__
    next(self.gen)
  File "/mnt/Data/Work/ForCity/alembic/alembic/operations/base.py", line 374, in batch_alter_table
    impl.flush()
  File "/mnt/Data/Work/ForCity/alembic/alembic/operations/batch.py", line 146, in flush
    batch_impl._create(self.impl)
  File "/mnt/Data/Work/ForCity/alembic/alembic/operations/batch.py", line 447, in _create
    op_impl.create_index(idx)
  File "/mnt/Data/Work/ForCity/alembic/alembic/ddl/impl.py", line 378, in create_index
    self._exec(schema.CreateIndex(index))
  File "/mnt/Data/Work/ForCity/alembic/alembic/ddl/impl.py", line 196, in _exec
    return conn.execute(construct, multiparams)
  File "/home/adrien/.virtualenvs/geoalchemy/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1306, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/home/adrien/.virtualenvs/geoalchemy/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
    return connection._execute_ddl(
  File "/home/adrien/.virtualenvs/geoalchemy/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1398, in _execute_ddl
    ret = self._execute_context(
  File "/home/adrien/.virtualenvs/geoalchemy/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/home/adrien/.virtualenvs/geoalchemy/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/home/adrien/.virtualenvs/geoalchemy/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/adrien/.virtualenvs/geoalchemy/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/home/adrien/.virtualenvs/geoalchemy/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: int_col
[SQL: CREATE INDEX idx_int_col ON the_table (int_col)]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

[CaselIT]

I think the expected usage is that in the batch the index is dropped and recreated with the expected columns

[zzzeek]

I think the expected usage is that in the batch the index is dropped and recreated with the expected columns

Hm, you mean explicitly right? I dont think autogenerate would have rendered it like that, though. a col rename + index should be working.

we do have a known limitation in this area for CHECK constraints: https://alembic.sqlalchemy.org/en/latest/batch.html#including-check-constraints where you need to do an explicit drop + recreate. this shouldn't have to apply to ordinary indexes that dont have expressions.

[zzzeek]

it works for UNIQUE though...hmmmmmmmmm how does it do that

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the main branch:

implement full copy for indexes in batch https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/3832

[adrien-berchet]

Thank you very much for this patch @zzzeek ! I will test it soon and let you know if it also solves my original issue.

[zzzeek]

i will merge it once the tests pass and you can test from github main

[zzzeek]

OK it's merged. now you can test as I only did a unit test, not an integration test

[adrien-berchet]

Hello,
I tested the main branch and it solves most of my issues, thank you very much!
I still have another issue but I am going to ping you where it is relevant.