should change of type + change of server default be heuristically orchestrated for incompatible server default types?

traceback に示されている PostgreSQL alter_column パス、特に alembic/ddl/postgresql.py から始め、型と server-default の変更がどのように出力されるかを追跡します。提供された op.alter_column の再現を検証ケースとして使用します。完了の条件は、互換性のない default と型の変更によって、必要な順序で実行可能な ALTER 文が生成されることです。

Describe the bug
I am changing a database model from handling UUIDs only in application code to reflecting it in the database model as well, hence SQLAlchemy is using PostgreSQL’s native UUID type. At the same time, I want to drop the existing DEFAULT '', which does not make sense for a NOT NULL column anyway[^1].

This results in Alembic rendering two ALTER statements in the wrong order:

ALTER TABLE users ALTER COLUMN uuid TYPE UUID USING uuid::uuid;

ALTER TABLE users ALTER COLUMN uuid DROP DEFAULT;

… which Postgres does not like:

sqlalchemy.exc.ProgrammingError: (psycopg.errors.DatatypeMismatch) default for column "uuid" cannot be cast automatically to type uuid
[SQL: ALTER TABLE users ALTER COLUMN uuid TYPE UUID USING uuid::uuid]

[^1]: The column used to be nullable in the legacy MariaDB system, while semantically being required in the application code—yay, legacy infrastructure.

Expected behavior
The order of the two ALTER statements should be flipped. Doing so manually works, and as far as I understand it should be atomic due to the transaction around anyway.

To Reproduce

diff --git a/app.py b/app.py
index 47fe855..33fdc7f 100644
--- a/app.py
+++ b/app.py
@@ -78 +79 @@ class User(db.Model):
-    uuid: Mapped[str] = mapped_column(String(40))
+    uuid: Mapped[UUID] = mapped_column(Uuid, default=uuid4)

def upgrade() -> None:
    """Upgrade schema."""
    op.alter_column(
        "users",
        "uuid",
        existing_type=sa.String(length=40),
        type_=sa.Uuid(),
        existing_nullable=False,
        existing_server_default=sa.text("''"),
        server_default=None,
        postgresql_using="uuid::uuid",
    )

Error

% alembic upgrade head
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 7ccc894a2927 -> 496b1c4ea40b, Convert uuid field to real UUID type
Traceback (most recent call last):
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/sqlalchemy/engine/default.py", line 942, in do_execute
    cursor.execute(statement, parameters)
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/psycopg/cursor.py", line 97, in execute
    raise ex.with_traceback(None)
psycopg.errors.DatatypeMismatch: default for column "uuid" cannot be cast automatically to type uuid

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

Traceback (most recent call last):
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/bin/alembic", line 10, in <module>
    sys.exit(main())
             ^^^^^^
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/alembic/config.py", line 636, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/alembic/config.py", line 626, in main
    self.run_cmd(cfg, options)
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/alembic/config.py", line 603, in run_cmd
    fn(
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/alembic/command.py", line 408, in upgrade
    script.run_env()
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/alembic/script/base.py", line 586, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/alembic/util/pyfiles.py", line 95, in load_python_file
    module = load_module_py(module_id, path)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/alembic/util/pyfiles.py", line 113, 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 "/home/qsx/RWTH/Afu/vpnadmin/migrations/env.py", line 88, in <module>
    run_migrations_online()
  File "/home/qsx/RWTH/Afu/vpnadmin/migrations/env.py", line 82, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/alembic/runtime/environment.py", line 946, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/alembic/runtime/migration.py", line 623, in run_migrations
    step.migration_fn(**kw)
  File "/home/qsx/RWTH/Afu/vpnadmin/migrations/versions/496b1c4ea40b_convert_uuid_field_to_real_uuid_type.py", line 23, in upgrade
    op.alter_column(
  File "<string>", line 8, in alter_column
  File "<string>", line 3, in alter_column
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/alembic/operations/ops.py", line 1956, in alter_column
    return operations.invoke(alt)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/alembic/operations/base.py", line 441, in invoke
    return fn(self, operation)
           ^^^^^^^^^^^^^^^^^^^
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/alembic/operations/toimpl.py", line 52, in alter_column
    operations.impl.alter_column(
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/alembic/ddl/postgresql.py", line 175, in alter_column
    self._exec(
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/alembic/ddl/impl.py", line 247, in _exec
    return conn.execute(construct, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
    return meth(
           ^^^^^
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/sqlalchemy/sql/ddl.py", line 180, in _execute_on_connection
    return connection._execute_ddl(
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/sqlalchemy/engine/base.py", line 1527, in _execute_ddl
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/sqlalchemy/engine/base.py", line 1843, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context
    self._handle_dbapi_exception(
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/sqlalchemy/engine/base.py", line 2352, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/sqlalchemy/engine/default.py", line 942, in do_execute
    cursor.execute(statement, parameters)
  File "/home/qsx/RWTH/Afu/vpnadmin/.venv/lib64/python3.11/site-packages/psycopg/cursor.py", line 97, in execute
    raise ex.with_traceback(None)
sqlalchemy.exc.ProgrammingError: (psycopg.errors.DatatypeMismatch) default for column "uuid" cannot be cast automatically to type uuid
[SQL: ALTER TABLE users ALTER COLUMN uuid TYPE UUID USING uuid::uuid]
(Background on this error at: https://sqlalche.me/e/20/f405)

Versions.

• OS: Fedora Linux 41
• Python: Python 3.11.12
• Alembic: alembic==1.15.1
• SQLAlchemy: sqlalchemy==2.0.39
• Database: PostgreSQL 16.8 (and MariaDB 10.11.11 for the legacy database)
• DBAPI: psycopg==3.2.6 (and mysqlclient==2.2.7 for the legacy database)

Additional context
I am migrating a legacy system with hand-written PHP and MySQL/MariaDB code, with many questionable choices (such as columns that should be NOT NULL and UNIQUE being neither, and the database does indeed have some entries that would violate this which were probably manually added at some time, or UUIDs not being real UUIDs). This system has to stay compatible with an external consumer by means of a SQL View (which luckily is not complicated at all). During the ongoing migration, it has to be compatible with both MariaDB and PostgreSQL[^2], the former can only be dropped once the migration and the external consumer is tested and complete.

[^2]: Well, and SQLite would be nice for the developers using Windows, where setting up a MariaDB and/or Postgres server is a bit more involved, but given the difficulties of migrations there and the additional complexity it would introduce, that’s really just a bonus.

Have a nice day!
Thanks, you too!