Online migration crashes when changing type from Text to REAL

[Bechma]

Describe the bug
Running the commands:
alembic revision --autogenerate
alembic upgrade head

with this configuration:

def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool
    )

    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            include_schemas=True,
            compare_server_default=True,
            compare_type=True,
        )

        with context.begin_transaction():
            context.run_migrations()

Expected behavior
No errors and changing the data type from text to real

To Reproduce
Having an existing table with a text column (with float values representation), change the data type from Text to REAL. Afterwards, run the migrations.

import sqlalchemy as sa
from alembic import op

# revision identifiers, used by Alembic.
revision = '36a16b8bc992'
down_revision = '58e041e2c164'
branch_labels = None
depends_on = None

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('t1', 'p1',
                    existing_type=sa.TEXT(),
                    type_=sa.REAL(),
                    existing_nullable=True,
                    schema='s1')
    op.alter_column('t1', 'p1',
                    existing_type=sa.TEXT(),
                    type_=sa.REAL(),
                    existing_nullable=True,
                    schema='s2')
    op.alter_column('t1', 'p1',
                    existing_type=sa.TEXT(),
                    type_=sa.REAL(),
                    existing_nullable=True,
                    schema='s3')
    op.alter_column('t1', 'p1',
                    existing_type=sa.TEXT(),
                    type_=sa.REAL(),
                    existing_nullable=True,
                    schema='s4')
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('t1', 'p1',
                    existing_type=sa.REAL(),
                    type_=sa.TEXT(),
                    existing_nullable=True,
                    schema='s4')
    op.alter_column('t1', 'p1',
                    existing_type=sa.REAL(),
                    type_=sa.TEXT(),
                    existing_nullable=True,
                    schema='s3')
    op.alter_column('t1', 'p1',
                    existing_type=sa.REAL(),
                    type_=sa.TEXT(),
                    existing_nullable=True,
                    schema='s2')
    op.alter_column('t1', 'p1',
                    existing_type=sa.REAL(),
                    type_=sa.TEXT(),
                    existing_nullable=True,
                    schema='s1')
    # ### end Alembic commands ###

Error

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 58e041e2c164 -> 36a16b8bc992, empty message
Traceback (most recent call last):
  File "...\site-packages\sqlalchemy\engine\base.py", line 1770, in _execute_context
    self.dialect.do_execute(
  File "...\site-packages\sqlalchemy\engine\default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.DatatypeMismatch: column "p1" cannot be cast automatically to type real
HINT:  You might need to specify "USING "p1"::real".


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

Traceback (most recent call last):
  File "...\lib\runpy.py", line 194, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "...\lib\runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "...\venv\Scripts\alembic.exe\__main__.py", line 7, in <module>
  File "...\site-packages\alembic\config.py", line 559, in main
    CommandLine(prog=prog).main(argv=argv)
  File "...\site-packages\alembic\config.py", line 553, in main
    self.run_cmd(cfg, options)
  File "...\site-packages\alembic\config.py", line 530, in run_cmd
    fn(
  File "...\site-packages\alembic\command.py", line 294, in upgrade
    script.run_env()
  File "...\site-packages\alembic\script\base.py", line 490, in run_env
    util.load_python_file(self.dir, "env.py")
  File "...\site-packages\alembic\util\pyfiles.py", line 97, in load_python_file
    module = load_module_py(module_id, path)
  File "...\site-packages\alembic\util\compat.py", line 184, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 848, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "...\alembic\env.py", line 135, in <module>
    run_migrations_online()
  File "...\alembic\env.py", line 129, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "...\site-packages\alembic\runtime\environment.py", line 813, in run_migrations
    self.get_context().run_migrations(**kw)
  File "...\site-packages\alembic\runtime\migration.py", line 561, in run_migrations
    step.migration_fn(**kw)
  File "...\alembic\versions\36a16b8bc992_.py", line 20, in upgrade
    op.alter_column('t1', 'p1',
  File "<string>", line 8, in alter_column
  File "<string>", line 3, in alter_column
  File "...\site-packages\alembic\operations\ops.py", line 1655, in alter_column
    return operations.invoke(alt)
  File "...\site-packages\alembic\operations\base.py", line 354, in invoke
    return fn(self, operation)
  File "...\site-packages\alembic\operations\toimpl.py", line 43, in alter_column
    operations.impl.alter_column(
  File "...\site-packages\alembic\ddl\postgresql.py", line 128, in alter_column
    self._exec(
  File "...\site-packages\alembic\ddl\impl.py", line 146, in _exec
    return conn.execute(construct, multiparams)
  File "...\site-packages\sqlalchemy\engine\base.py", line 1262, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "...\site-packages\sqlalchemy\sql\ddl.py", line 77, in _execute_on_connection
    return connection._execute_ddl(
  File "...\site-packages\sqlalchemy\engine\base.py", line 1352, in _execute_ddl
    ret = self._execute_context(
  File "...\site-packages\sqlalchemy\engine\base.py", line 1813, in _execute_context
    self._handle_dbapi_exception(
  File "...\site-packages\sqlalchemy\engine\base.py", line 1994, in _handle_dbapi_exception
    util.raise_(
  File "...\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "...\site-packages\sqlalchemy\engine\base.py", line 1770, in _execute_context
    self.dialect.do_execute(
  File "...\site-packages\sqlalchemy\engine\default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DatatypeMismatch) column "p1" cannot be cast automatically to type real
HINT:  You might need to specify "USING "p1"::real".

[SQL: ALTER TABLE jira_autodata."Sprints_backlog" ALTER COLUMN "p1" TYPE REAL ]
(Background on this error at: http://sqlalche.me/e/14/f405)

Versions.

• OS: Windows
• Python: 3.8.10
• Alembic: 1.6.5
• SQLAlchemy: 1.4.18
• Database: Postgres 12
• DBAPI: psycopg2

Additional context

Have a nice day!

[zzzeek]

hey there -

PG's USING is supported using postgresql_using:

 op.alter_column('t1', 'p1',
                    existing_type=sa.TEXT(),
                    type_=sa.REAL(),
                    existing_nullable=True,
                    schema='s1',
                    postgresql_using='"p1"::real')

[Bechma]

should alembic include it automatically during an online migration or is it a very hard to automate?

[zzzeek]

we find that adding too much implicit decision making makes for a library that is less easy to understand and maintain. additionally, PostgreSQL itself arguably could make this decision automatically as well, to cast the text to a real seems pretty obvious, yet for whatever reason they haven't.