Adding column with batch_alter_table and recreate='always' fails if table contains a boolean

[sronsiek]

Describe the bug

This best described by (working) example. Consider this migration:

"""alembic_test

Revision ID: 001
Revises: 
Create Date: 2020-12-27 16:31:49.844972

"""
from alembic import op
import sqlalchemy as sa

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

def upgrade():
    op.create_table('alembic_test',
        sa.Column('id', sa.Integer(), nullable=False),
        # sa.Column('bool_col', sa.Boolean(), server_default='false', nullable=False),
        sa.PrimaryKeyConstraint('id')
    )

    with op.batch_alter_table('alembic_test', recreate='always') as b:
        b.add_column(sa.Column('newcol', type_=sa.Integer(), nullable=True), insert_before='id')

def downgrade():
    op.drop_table('alembic_test')

The op.create_table definition in this migration was auto-generated by alembic.

The migration works as expected while the boolean Column definition is commented out - ie it creates the alembic_test table, then inserts column newcol before the id column.

However, uncomment boolean column definition and it fails:

sqlalchemy.exc.CompileError: Can't emit DROP CONSTRAINT for constraint CheckConstraint(<sqlalchemy.sql.elements.BinaryExpression object at 0x7ff38df967f0>, name='_unnamed_'
...
); it has no name

Full error stack below.

Expected behavior
The migration should work regardless of whether the table contains booleans or not.

To Reproduce
Run the provided migration

Error

1. Running with --sql:

# alembic upgrade --sql 001

-- Running upgrade 001

CREATE TABLE alembic_test (
    id SERIAL NOT NULL, 
    bool_col BOOLEAN, 
    PRIMARY KEY (id)
);

Traceback (most recent call last):
  File "/usr/local/bin/alembic", line 10, in <module>
    sys.exit(main())
  File "/usr/local/lib/python3.5/site-packages/alembic/config.py", line 581, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/usr/local/lib/python3.5/site-packages/alembic/config.py", line 575, in main
    self.run_cmd(cfg, options)
  File "/usr/local/lib/python3.5/site-packages/alembic/config.py", line 555, in run_cmd
    **dict((k, getattr(options, k, None)) for k in kwarg)
  File "/usr/local/lib/python3.5/site-packages/alembic/command.py", line 298, in upgrade
    script.run_env()
  File "/usr/local/lib/python3.5/site-packages/alembic/script/base.py", line 489, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/usr/local/lib/python3.5/site-packages/alembic/util/pyfiles.py", line 98, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python3.5/site-packages/alembic/util/compat.py", line 184, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 697, in exec_module
  File "<frozen importlib._bootstrap>", line 222, in _call_with_frames_removed
  File "/opt/logbook/migrations/alembic/env.py", line 86, in <module>
    run_migrations_offline()
  File "/opt/logbook/migrations/alembic/env.py", line 60, in run_migrations_offline
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/local/lib/python3.5/site-packages/alembic/runtime/environment.py", line 846, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python3.5/site-packages/alembic/runtime/migration.py", line 522, in run_migrations
    step.migration_fn(**kw)
  File "/opt/logbook/migrations/alembic/versions/004_alembic_test.py", line 27, in upgrade
    b.add_column(sa.Column('newcol', type_=sa.Integer(), nullable=True), insert_before='id')
  File "/usr/local/lib/python3.5/contextlib.py", line 66, in __exit__
    next(self.gen)
  File "/usr/local/lib/python3.5/site-packages/alembic/operations/base.py", line 353, in batch_alter_table
    impl.flush()
  File "/usr/local/lib/python3.5/site-packages/alembic/operations/batch.py", line 100, in flush
    **self.reflect_kwargs
  File "<string>", line 2, in __new__
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/util/deprecations.py", line 139, in warned
    return fn(*args, **kwargs)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 560, in __new__
    metadata._remove_table(name, schema)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    with_traceback=exc_tb,
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 555, in __new__
    table._init(name, metadata, *args, **kw)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 649, in _init
    resolve_fks=resolve_fks,
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 667, in _autoload
    autoload_with.run_callable(
AttributeError: 'MockConnection' object has no attribute 'run_callable'

2. Running without --sql:

# alembic upgrade 001

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 003 -> 004, alembic_test
INFO  [alembic.ddl.postgresql] Detected sequence named 'alembic_test_id_seq' as owned by integer column 'alembic_test(id)', assuming SERIAL and omitting
Traceback (most recent call last):
  File "/usr/local/bin/alembic", line 10, in <module>
    sys.exit(main())
  File "/usr/local/lib/python3.5/site-packages/alembic/config.py", line 581, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/usr/local/lib/python3.5/site-packages/alembic/config.py", line 575, in main
    self.run_cmd(cfg, options)
  File "/usr/local/lib/python3.5/site-packages/alembic/config.py", line 555, in run_cmd
    **dict((k, getattr(options, k, None)) for k in kwarg)
  File "/usr/local/lib/python3.5/site-packages/alembic/command.py", line 298, in upgrade
    script.run_env()
  File "/usr/local/lib/python3.5/site-packages/alembic/script/base.py", line 489, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/usr/local/lib/python3.5/site-packages/alembic/util/pyfiles.py", line 98, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python3.5/site-packages/alembic/util/compat.py", line 184, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 697, in exec_module
  File "<frozen importlib._bootstrap>", line 222, in _call_with_frames_removed
  File "/opt/logbook/migrations/alembic/env.py", line 88, in <module>
    run_migrations_online()
  File "/opt/logbook/migrations/alembic/env.py", line 82, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/local/lib/python3.5/site-packages/alembic/runtime/environment.py", line 846, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python3.5/site-packages/alembic/runtime/migration.py", line 522, in run_migrations
    step.migration_fn(**kw)
  File "/opt/logbook/migrations/alembic/versions/004_alembic_test.py", line 27, in upgrade
    b.add_column(sa.Column('newcol', type_=sa.Integer(), nullable=True), insert_before='id')
  File "/usr/local/lib/python3.5/contextlib.py", line 66, in __exit__
    next(self.gen)
  File "/usr/local/lib/python3.5/site-packages/alembic/operations/base.py", line 353, in batch_alter_table
    impl.flush()
  File "/usr/local/lib/python3.5/site-packages/alembic/operations/batch.py", line 116, in flush
    batch_impl._create(self.impl)
  File "/usr/local/lib/python3.5/site-packages/alembic/operations/batch.py", line 357, in _create
    op_impl.prep_table_for_batch(self.table)
  File "/usr/local/lib/python3.5/site-packages/alembic/ddl/postgresql.py", line 48, in prep_table_for_batch
    self.drop_constraint(constraint)
  File "/usr/local/lib/python3.5/site-packages/alembic/ddl/impl.py", line 248, in drop_constraint
    self._exec(schema.DropConstraint(const))
  File "/usr/local/lib/python3.5/site-packages/alembic/ddl/impl.py", line 141, in _exec
    return conn.execute(construct, *multiparams, **params)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1066, in _execute_ddl
    else None,
  File "<string>", line 1, in <lambda>
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", line 481, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 29, in _compiler
    return dialect.ddl_compiler(dialect, self, **kw)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 322, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 352, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/ext/compiler.py", line 441, in <lambda>
    lambda *arg, **kw: existing(*arg, **kw),
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/ext/compiler.py", line 486, in __call__
    return fn(element, compiler, **kw)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/ext/compiler.py", line 425, in _wrap_existing_dispatch
    return existing_dispatch(element, compiler, **kw)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 96, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 3162, in visit_drop_constraint
    "it has no name" % drop.element
sqlalchemy.exc.CompileError: Can't emit DROP CONSTRAINT for constraint CheckConstraint(<sqlalchemy.sql.elements.BinaryExpression object at 0x7fc217b04358>, name='_unnamed_', table=Table('alembic_test', MetaData(bind=None), Column('id', INTEGER(), table=<alembic_test>, primary_key=True, nullable=False), Column('bool_col', BOOLEAN(), table=<alembic_test>), schema=None), _create_rule=<sqlalchemy.util.langhelpers.portable_instancemethod object at 0x7fc217b16e10>, _type_bound=True); it has no name

Versions.

• OS: Running in official python docker image python:3.5-alpine3.8
• Python: 3.5
• Alembic: 1.4.3
• SQLAlchemy: 1.3.22
• Database: Postgres 9.5
• DBAPI: psycopg2 2.8.6

Additional context
None

[zzzeek]

batch support for Postgresql is extremely limited as this feature was intended only for SQLite. PG supports ALTER TABLE directly. The issue also won't occur with SQLAlchemy 1.4 as create_constraint defaults to False on the boolean datatype. "--sql" mode as documented will not work with batch mode unless copy_from is specified.

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the master branch:

Consult ApplyBatchImpl for hints on constraints to drop https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/2438