sqlalchemy/alembic

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

Open

#963 ouverte le 10 nov. 2021

Voir sur GitHub
 (12 commentaires) (0 réactions) (0 assignés)Python (210 forks)batch import
autogenerate - renderingcookbook requestedhelp wantedpostgresqluse case

Métriques du dépôt

Stars
 (2 300 stars)
Métriques de merge PR
 (Merge moyen 8j 9h) (1 PR mergée en 30 j)

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 !

Guide contributeur

Direction de recherche
La correction se trouve probablement dans le code d'auto génération d'Alembic, en particulier dans la manière dont les changements de type sont détectés et rendus. Examinez les modules `alembic/autogenerate/compare.py` ou `alembic/autogenerate/render.py`. Le problème est que lorsqu'un type passe de VARCHAR à UUID, le paramètre `postgresql using` n'est pas ajouté à `op.alter column`. Étudiez comment d'autres changements de type gèrent `postgresql using` et ajoutez une logique similaire. Consultez les commentaires existants dans l'issue pour plus d'indices.
Stack technique
pythonpostgresql
Domaine
database
Type d'issue
Bug
DifficultéDifficulté estimée pour un nouveau contributeur, de 1 pour un très petit changement à 5 pour un travail expert.
3
Temps estiméFourchette de temps approximative pour investiguer, implémenter, tester et préparer une pull request.
Une demi journée
Statut d'activitéDisponibilité apparente de l'issue : fraîche, active, ancienne, bloquée ou en attente d'un mainteneur.
Active
ClartéClarté avec laquelle l'issue explique le changement attendu, les critères d'acceptation et la prochaine étape.
Claire
Prérequis
PythonGitPostgreSQL
Accessibilité débutantScore de 1 à 100 estimant l'accessibilité de cette issue pour un premier contributeur.
65