Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

create_type=false still creating enum and consequently failing on duplicate #1347

Closed
Faolain opened this issue Nov 7, 2023 · 9 comments
Closed

Comments

@Faolain
Copy link

Faolain commented Nov 7, 2023

Describe the bug

When creating a table with an already existing Enum and setting the create_type=False such as the below

op.create_table(
        "batches",
        sa.Column("id", sa.GUID(length=16), nullable=False),
        sa.Column(
            "status",
            sa.Enum("pending", "finished", "failed", name="assessmentstatusenum", create_type=False),
            nullable=False,
        )

I see the error:

sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.DuplicateObjectError'>: type "assessmentstatusenum" already exists
[SQL: CREATE TYPE assessmentstatusenum AS ENUM ('pending', 'finished', 'failed')]
(Background on this error at: https://sqlalche.me/e/20/f405)

Expected behavior

The Enum should be referenced on the column without attempting to create the Enum, and therefore shouldn't cause the migration to fail.

Error

Starting database upgrade process ─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Are you sure you you want migrate the database to the `head` revision? [y/n]: y
2023-11-07T19:05:09.401677Z [info     ] Context impl PostgresqlImpl.
2023-11-07T19:05:09.401765Z [info     ] Will assume transactional DDL.
2023-11-07T19:05:09.443335Z [info     ] Running upgrade c3a9a11cc35d -> fb38e91221ac, create base models
Traceback (most recent call last):
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 552, in _prepare_and_execute
    self._rows = await prepared_stmt.fetch(*parameters)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/asyncpg/prepared_stmt.py", line 176, in fetch
    data = await self.__bind_execute(args, 0, timeout)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/asyncpg/prepared_stmt.py", line 241, in __bind_execute
    data, status, _ = await self.__do_execute(
                      ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/asyncpg/prepared_stmt.py", line 230, in __do_execute
    return await executor(protocol)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "asyncpg/protocol/protocol.pyx", line 201, in bind_execute
asyncpg.exceptions.DuplicateObjectError: type "assessmentstatusenum" already exists

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

Traceback (most recent call last):
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 586, in execute
    self._adapt_connection.await_(
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 125, in await_only
    return current.driver.switch(awaitable)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 185, in greenlet_spawn
    value = await result
            ^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 564, in _prepare_and_execute
    self._handle_exception(error)
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 515, in _handle_exception
    self._adapt_connection._handle_exception(error)
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 803, in _handle_exception
    raise translated_error from error
sqlalchemy.dialects.postgresql.asyncpg.AsyncAdapt_asyncpg_dbapi.ProgrammingError: <class 'asyncpg.exceptions.DuplicateObjectError'>: type "assessmentstatusenum" already exists

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

Traceback (most recent call last):
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/bin/app", line 8, in <module>
    sys.exit(run_cli())
             ^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/litestar/__main__.py", line 6, in run_cli
    litestar_group()
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/rich_click/rich_command.py", line 126, in main
    rv = self.invoke(ctx)
         ^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/click/decorators.py", line 33, in new_func
    return f(get_current_context(), *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/litestar/cli/_utils.py", line 272, in wrapped
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/click/decorators.py", line 33, in new_func
    return f(get_current_context(), *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/litestar/cli/_utils.py", line 272, in wrapped
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/advanced_alchemy/extensions/litestar/cli.py", line 109, in upgrade_database
    alembic_commands.upgrade(revision=revision, sql=sql, tag=tag)
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/advanced_alchemy/alembic/commands.py", line 92, in upgrade
    return migration_command.upgrade(config=self.config, revision=revision, tag=tag, sql=sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/alembic/command.py", line 399, in upgrade
    script.run_env()
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/alembic/script/base.py", line 578, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 93, in load_python_file
    module = load_module_py(module_id, path)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 109, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<frozen importlib._bootstrap_external>", line 940, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/src/app/lib/db/migrations/env.py", line 143, in <module>
    asyncio.run(run_migrations_online())
  File "/Users/faolain/.pyenv/versions/3.11.4/lib/python3.11/asyncio/runners.py", line 190, in run
    return runner.run(main)
           ^^^^^^^^^^^^^^^^
  File "/Users/faolain/.pyenv/versions/3.11.4/lib/python3.11/asyncio/runners.py", line 118, in run
    return self._loop.run_until_complete(task)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/.pyenv/versions/3.11.4/lib/python3.11/asyncio/base_events.py", line 653, in run_until_complete
    return future.result()
           ^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/src/app/lib/db/migrations/env.py", line 135, in run_migrations_online
    await connection.run_sync(do_run_migrations)
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/ext/asyncio/engine.py", line 886, in run_sync
    return await greenlet_spawn(fn, self._proxied, *arg, **kw)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 190, in greenlet_spawn
    result = context.throw(*sys.exc_info())
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/src/app/lib/db/migrations/env.py", line 105, in do_run_migrations
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/alembic/runtime/environment.py", line 937, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/alembic/runtime/migration.py", line 624, in run_migrations
    step.migration_fn(**kw)
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/src/app/lib/db/migrations/versions/2023-11-03_create_base_models_fb38e91221ac.py", line 38, in upgrade
    schema_upgrades()
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/src/app/lib/db/migrations/versions/2023-11-03_create_base_models_fb38e91221ac.py", line 391, in schema_upgrades
    op.create_table(
  File "<string>", line 8, in create_table
  File "<string>", line 3, in create_table
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/alembic/operations/ops.py", line 1305, in create_table
    return operations.invoke(op)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/alembic/operations/base.py", line 393, in invoke
    return fn(self, operation)
           ^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/alembic/operations/toimpl.py", line 128, in create_table
    operations.impl.create_table(table)
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/alembic/ddl/impl.py", line 351, in create_table
    table.dispatch.before_create(
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/event/attr.py", line 487, in __call__
    fn(*args, **kw)
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 846, in __call__
    return getattr(self.target, self.name)(*arg, **kw)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/sql/sqltypes.py", line 1162, in _on_table_create
    t._on_table_create(target, bind, **kw)
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/named_types.py", line 98, in _on_table_create
    self.create(bind=bind, checkfirst=checkfirst)
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/named_types.py", line 339, in create
    super().create(bind, checkfirst=checkfirst)
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/named_types.py", line 51, in create
    bind._run_ddl_visitor(self.DDLGenerator, self, checkfirst=checkfirst)
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2447, in _run_ddl_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/sql/visitors.py", line 671, in traverse_single
    return meth(obj, **kw)
           ^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/named_types.py", line 153, in visit_enum
    self.connection.execute(CreateEnumType(enum))
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
    return meth(
           ^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/sql/ddl.py", line 181, in _execute_on_connection
    return connection._execute_ddl(
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1528, in _execute_ddl
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context
    self._handle_dbapi_exception(
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2343, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 586, in execute
    self._adapt_connection.await_(
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 125, in await_only
    return current.driver.switch(awaitable)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 185, in greenlet_spawn
    value = await result
            ^^^^^^^^^^^^
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 564, in _prepare_and_execute
    self._handle_exception(error)
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 515, in _handle_exception
    self._adapt_connection._handle_exception(error)
  File "/Users/faolain/Desktop/test-monorepo/packages/backend/.venv/lib/python3.11/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 803, in _handle_exception
    raise translated_error from error
sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.DuplicateObjectError'>: type "assessmentstatusenum" already exists
[SQL: CREATE TYPE assessmentstatusenum AS ENUM ('pending', 'finished', 'failed')]
(Background on this error at: https://sqlalche.me/e/20/f405)

Versions.

  • OS: MacOS 13.2 (22D49)
  • Python: 3.11.6
  • Alembic: 1.12.0
  • SQLAlchemy: 2.0.22
  • Database: Postgres
  • DBAPI:

Additional context

Have a nice day!

@Faolain Faolain added the requires triage New issue that requires categorization label Nov 7, 2023
@zzzeek zzzeek added expected behavior and removed requires triage New issue that requires categorization labels Nov 7, 2023
@zzzeek
Copy link
Member

zzzeek commented Nov 7, 2023

the create_type argument only applies to postgresql.ENUM(), not SQLAlchemy Enum ; the argument is ignored otherwise. The reason the type accepts **kw right now is due to Python 3.7 not accepting positional-only arguments, which will be finally changed in SQLAlchemy 2.1 that targets 3.8 and above.

for current functionality the migration would look like this:

    op.create_table(
        "batches",
        sa.Column("id", sa.UUID(), nullable=False),
        sa.Column(
            "status",
            sa.Enum(
                "pending", "finished", "failed", name="assessmentstatusenum"
            ).with_variant(
                postgresql.ENUM(
                    "pending",
                    "finished",
                    "failed",
                    name="assessmentstatusenum",
                    create_type=False,
                ),
                "postgresql",
            ),
            nullable=False,
        ),
    )

@zzzeek zzzeek closed this as not planned Won't fix, can't repro, duplicate, stale Nov 7, 2023
@zzzeek
Copy link
Member

zzzeek commented Nov 8, 2023

see sqlalchemy/sqlalchemy#10604 for SQLAlchemy 2.1

@JoADM
Copy link

JoADM commented Jul 25, 2024

have you fixed this? I have the same problem with my migration

sa.Column('investment_type', sa.Enum('CROWD_FUNDING', 'OPEN_MARKET', 'MANAGE', name='investmenttype', create_type=False), nullable=False),

same error message, im running python3.12 and alembic 1.13.2

@CaselIT
Copy link
Member

CaselIT commented Jul 26, 2024

See #1347 (comment)

@alexindev
Copy link

have you fixed this? I have the same problem with my migration

sa.Column('investment_type', sa.Enum('CROWD_FUNDING', 'OPEN_MARKET', 'MANAGE', name='investmenttype', create_type=False), nullable=False),

same error message, im running python3.12 and alembic 1.13.2

use postgresql.ENUM instead of sa.Enum:

sa.Column('investment_type', postgresql.ENUM('CROWD_FUNDING', 'OPEN_MARKET', 'MANAGE', name='investmenttype', create_type=False), nullable=False),

@WSAyan
Copy link

WSAyan commented Oct 28, 2024

Why this issue is closed? Isn't it possible to create enum types before tables? Why tables are created before types even if I use timestamps on files?

@CaselIT
Copy link
Member

CaselIT commented Oct 28, 2024

Sure, but this issue was of the opposite, meaning the create type was issued when none was wanted

@Northshoot
Copy link

surprised that the issue closed, it is still the problem with Alembic trying to create type when it is already exists and preventing from migration.

I just had the same issues and had to manually update script to postgresql.ENUM and add create_type=False .

Would assume that this should be handled automatically with the migration script.

@zzzeek
Copy link
Member

zzzeek commented Nov 19, 2024

hi all -

this is not an alembic issue. The issue you all want to look at is at SQLAlchemy at sqlalchemy/sqlalchemy#10604 where we will add "create_type" to the base Enum type.

locking this issue now

@sqlalchemy sqlalchemy locked as resolved and limited conversation to collaborators Nov 19, 2024
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Projects
None yet
Development

No branches or pull requests

7 participants