sqlalchemy/alembic

Autogenerate does not use of postgresql_using when changing a type to UUID

Open

#963 opened on Nov 10, 2021

View on GitHub
 (12 comments) (0 reactions) (0 assignees)Python (2,300 stars) (210 forks)batch import
autogenerate - renderingcookbook requestedhelp wantedpostgresqluse case

Description

Describe the bug When migrating a column from VARCHAR to UUID, autogenerate does not include the postgresql_using keyword argument to the op.alter_column call. This leads to an error when applying the migration with alembic upgrade head

Expected behavior The autogenerator should include postgresql_using="<column_name>::uuid", to the alter_column call, allowing the

To Reproduce

Class before change

class Subscription(Base):
    __tablename__ = "subscription"

    id = Column(Integer, primary_key=True)
    remote_uuid = Column(String(255), nullable=False, index=True)

Class after change

class Subscription(Base):
    __tablename__ = "subscription"

    id = Column(Integer, primary_key=True)
    remote_uuid = Column(UUID(as_uuid=True), nullable=False, index=True)

Migration script generated:

"""empty message

Revision ID: 1
Revises: 979600339054
Create Date: 2021-11-10 14:22:34.203919+00:00

"""
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

from alembic import op

# revision identifiers, used by Alembic.
revision = "1"
down_revision = "979600339054"
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column(
        "subscription",
        "remote_uuid",
        existing_type=sa.VARCHAR(length=255),
        type_=postgresql.UUID(as_uuid=True),
        existing_nullable=False,
    )
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column(
        "subscription",
        "remote_uuid",
        existing_type=postgresql.UUID(as_uuid=True),
        type_=sa.VARCHAR(length=255),
        existing_nullable=False,
    )
    # ### 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 979600339054 -> 1, empty message
Traceback (most recent call last):
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1799, in _execute_context
    self.dialect.do_execute(
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.DatatypeMismatch: column "remote_uuid" cannot be cast automatically to type uuid
HINT:  You might need to specify "USING remote_uuid::uuid".


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

Traceback (most recent call last):
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/bin/alembic", line 8, in <module>
    sys.exit(main())
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/alembic/config.py", line 588, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/alembic/config.py", line 582, in main
    self.run_cmd(cfg, options)
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/alembic/config.py", line 559, in run_cmd
    fn(
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/alembic/command.py", line 320, in upgrade
    script.run_env()
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/alembic/script/base.py", line 563, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 92, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 108, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 883, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/alembic/env.py", line 80, in <module>
    run_migrations_online()
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/alembic/env.py", line 74, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/alembic/runtime/environment.py", line 851, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/alembic/runtime/migration.py", line 620, in run_migrations
    step.migration_fn(**kw)
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/alembic/versions/v1_2021-11-10.py", line 24, in upgrade
    op.alter_column(
  File "<string>", line 8, in alter_column
  File "<string>", line 3, in alter_column
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/alembic/operations/ops.py", line 1880, in alter_column
    return operations.invoke(alt)
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/alembic/operations/base.py", line 392, in invoke
    return fn(self, operation)
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/alembic/operations/toimpl.py", line 50, in alter_column
    operations.impl.alter_column(
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/alembic/ddl/postgresql.py", line 144, in alter_column
    self._exec(
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/alembic/ddl/impl.py", line 197, in _exec
    return conn.execute(construct, multiparams)
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1286, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 77, in _execute_on_connection
    return connection._execute_ddl(
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1378, in _execute_ddl
    ret = self._execute_context(
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1842, in _execute_context
    self._handle_dbapi_exception(
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2023, in _handle_dbapi_exception
    util.raise_(
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1799, in _execute_context
    self.dialect.do_execute(
  File "/home/aegea/Work/aizo-cloud/aizo_db_models/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DatatypeMismatch) column "remote_uuid" cannot be cast automatically to type uuid
HINT:  You might need to specify "USING remote_uuid::uuid".

[SQL: ALTER TABLE subscription ALTER COLUMN remote_uuid TYPE UUID ]
(Background on this error at: https://sqlalche.me/e/14/f405)

Versions.

  • OS: Ubuntu 20.04
  • Python: 3.10
  • Alembic: 1.7.4
  • SQLAlchemy: 1.4.26
  • Database: PostgreSQL 12.8
  • DBAPI: 2.0 I guess ? I assume this is the one provided by my psycopg2-binary 2.9.1

Additional context

Note that the migration does work when adding postgresql_using="remote_uuid::uuid" to the migration script.

Have a nice day! You too !

Contributor guide

Tech stack
pythonpostgresql
Domain
backenddatabase
Issue type
bug
DifficultyEstimated implementation difficulty for a new contributor, from 1 for very small changes to 5 for expert-level work.
3
Estimated timeA rough time range for an experienced contributor to investigate, implement, test, and prepare a pull request.
half day
Activity statusHow available the issue appears right now: fresh, active, stale, blocked, or waiting on maintainer input.
active
ClarityHow clearly the issue explains the expected change, acceptance criteria, and next step.
clear
Prerequisites
Basic knowledge of PythonFamiliarity with Alembic's autogenerate process
Newbie friendlinessA 1-100 score estimating how approachable this issue is for first-time contributors.
60
Research direction
The fix likely resides in Alembic's autogenerate code, specifically in how type changes are detected and rendered. Look at the `alembic/autogenerate/compare.py` or `alembic/autogenerate/render.py` modules. The issue is that when a type changes from VARCHAR to UUID, the `postgresql using` parameter is not added to `op.alter column`. Investigate how other type changes handle `postgresql using` and add similar logic. Review existing comments in the issue for further hints.