Autogenerated migrations do not drop created types on downgrade.

[sqlalchemy-bot]

Migrated issue, originally created by Connor Wolf (fake-name)

Basically the title. I have a number of postgresql ENUM types, and they break autogenerated migrations.

The following test-case shows this:

import settings
from sqlalchemy import create_engine

from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

from sqlalchemy.dialects.postgresql import ENUM
item_validation_enum   = ENUM('unverified', 'valid', 'rejected',          name='item_validation_enum')

class Wat(Base):
    __tablename__ = 'blooooop'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    name = Column(item_validation_enum)

SQLALCHEMY_DATABASE_URI = 'postgresql://{user}:{passwd}@{host}:5432/{database}'.format(
    user     =settings.NEW_DATABASE_USER,
    passwd   =settings.NEW_DATABASE_PASS,
    host     =settings.NEW_DATABASE_IP,
    database =settings.NEW_DATABASE_DB_NAME)

engine = create_engine(SQLALCHEMY_DATABASE_URI, pool_size = 5, isolation_level='REPEATABLE_READ')
SessionFactory = sessionmaker(bind=engine, autoflush=False, autocommit=False)
session = scoped_session(SessionFactory)

(settings.py just contains my db login creds, it's left out for obvious reasons)

Generates the following migration:

"""empty message

Revision ID: 99d5038f535d
Revises: 
Create Date: 2018-03-01 22:12:09.757465

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

# revision identifiers, used by Alembic.
revision = '99d5038f535d'
down_revision = None
branch_labels = None
depends_on = None

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('blooooop',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', postgresql.ENUM('unverified', 'valid', 'rejected', name='item_validation_enum'), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ###

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('blooooop')
    # ### end Alembic commands ###

Trying to migrate -> head -> base -> head causes it to fail.

herp@mainnas:/media/Storage/Scripts/alembtest$ alembic upgrade head
/usr/local/lib/python3.4/dist-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
  """)
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 99d5038f535d, empty message
herp@mainnas:/media/Storage/Scripts/alembtest$ alembic downgrade base
/usr/local/lib/python3.4/dist-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
  """)
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade 99d5038f535d -> , empty message
herp@mainnas:/media/Storage/Scripts/alembtest$ alembic upgrade head
/usr/local/lib/python3.4/dist-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
  """)
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 99d5038f535d, empty message
Traceback (most recent call last):
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
psycopg2.ProgrammingError: type "item_validation_enum" already exists

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

Traceback (most recent call last):
  File "/usr/local/bin/alembic", line 11, in <module>
    load_entry_point('alembic==0.9.8', 'console_scripts', 'alembic')()
  File "/usr/local/lib/python3.4/dist-packages/alembic/config.py", line 479, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/usr/local/lib/python3.4/dist-packages/alembic/config.py", line 473, in main
    self.run_cmd(cfg, options)
  File "/usr/local/lib/python3.4/dist-packages/alembic/config.py", line 456, in run_cmd
    **dict((k, getattr(options, k, None)) for k in kwarg)
  File "/usr/local/lib/python3.4/dist-packages/alembic/command.py", line 254, in upgrade
    script.run_env()
  File "/usr/local/lib/python3.4/dist-packages/alembic/script/base.py", line 427, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/usr/local/lib/python3.4/dist-packages/alembic/util/pyfiles.py", line 81, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python3.4/dist-packages/alembic/util/compat.py", line 97, in load_module_py
    module_id, path).load_module(module_id)
  File "<frozen importlib._bootstrap>", line 539, in _check_name_wrapper
  File "<frozen importlib._bootstrap>", line 1614, in load_module
  File "<frozen importlib._bootstrap>", line 596, in _load_module_shim
  File "<frozen importlib._bootstrap>", line 1220, in load
  File "<frozen importlib._bootstrap>", line 1200, in _load_unlocked
  File "<frozen importlib._bootstrap>", line 1129, in _exec
  File "<frozen importlib._bootstrap>", line 1471, in exec_module
  File "<frozen importlib._bootstrap>", line 321, in _call_with_frames_removed
  File "alembic/env.py", line 81, in <module>
    run_migrations_online()
  File "alembic/env.py", line 76, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/local/lib/python3.4/dist-packages/alembic/runtime/environment.py", line 836, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python3.4/dist-packages/alembic/runtime/migration.py", line 330, in run_migrations
    step.migration_fn(**kw)
  File "/media/Storage/Scripts/alembtest/alembic/versions/99d5038f535d_.py", line 24, in upgrade
    sa.PrimaryKeyConstraint('id')
  File "<string>", line 8, in create_table
  File "<string>", line 3, in create_table
  File "/usr/local/lib/python3.4/dist-packages/alembic/operations/ops.py", line 1120, in create_table
    return operations.invoke(op)
  File "/usr/local/lib/python3.4/dist-packages/alembic/operations/base.py", line 319, in invoke
    return fn(self, operation)
  File "/usr/local/lib/python3.4/dist-packages/alembic/operations/toimpl.py", line 101, in create_table
    operations.impl.create_table(table)
  File "/usr/local/lib/python3.4/dist-packages/alembic/ddl/impl.py", line 193, in create_table
    _ddl_runner=self)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/event/attr.py", line 284, in __call__
    fn(*args, **kw)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/langhelpers.py", line 559, in __call__
    return getattr(self.target, self.name)(*arg, **kw)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/dialects/postgresql/base.py", line 1337, in _on_table_create
    self.create(bind=bind, checkfirst=checkfirst)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/dialects/postgresql/base.py", line 1285, in create
    bind.execute(CreateEnumType(self))
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 1009, in _execute_ddl
    compiled
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) type "item_validation_enum" already exists
 [SQL: "CREATE TYPE item_validation_enum AS ENUM ('unverified', 'valid', 'rejected')"] (Background on this error at: http://sqlalche.me/e/f405)

Obviously, this is pretty minor, but it can be annoying.

Target DB is PostgreSQL 9.6

As a side note, --raiseerr has some issues too:

herp@mainnas:/media/Storage/Scripts/alembtest$ alembic upgrade head --raiseerr
usage: alembic [-h] [-c CONFIG] [-n NAME] [-x X] [--raiseerr]
               {branches,current,downgrade,edit,heads,history,init,list_templates,merge,revision,show,stamp,upgrade}
               ...
alembic: error: unrecognized arguments: --raiseerr

In this case, it's just positional (--raiseerr has to be before the command), but that's discordant with most CLI tools.

[sqlalchemy-bot]

Connor Wolf (fake-name) wrote:

Arrrgh, I swear I searched drop enum types before filing this, but now I find https://bitbucket.org/zzzeek/alembic/issues/53/create-table-dont-create-enum AND https://bitbucket.org/zzzeek/alembic/issues/67/autogenerate-with-enums-on-postgres-dont.

It still seems to me that if autogenerate can create enums, it should also destroy them.

[sqlalchemy-bot]

Connor Wolf (fake-name) wrote:

Actually, is there a legitimate reason all the type creation/destruction doesn't default to checkfirst=True?

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

the official dupe here is #278. no resources to work on this, sorry. I hope to move the issue tracker to github in the near future to facilitate search.

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

In this case, it's just positional (--raiseerr has to be before the command), but that's discordant with most CLI tools.

This is standard Python argparse behavior and is pretty widespread, https://stackoverflow.com/questions/10896211/python-argument-parser-that-handles-global-options-to-sub-commands-properly

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

Duplicate of #278.

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

Actually, is there a legitimate reason all the type creation/destruction doesn't default to checkfirst=True?

the individual methods do http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.ENUM.create

at the metadata level the postgresql.ENUM is trying to be compatible with the generic sqltypes.Enum. The confusion of PG's ENUM type not fitting in with the way database types work throughout the rest of the SQL ecosystem is part of why I'm not in a hurry to try and figure out #278.

[sqlalchemy-bot]

Changes by Connor Wolf (fake-name):

• edited description

[sqlalchemy-bot]

Changes by Michael Bayer (zzzeek):

• added labels: duplicate