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

[qsuscs]

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¹.

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]

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², the former can only be dropped once the migration and the external consumer is tested and complete.

Have a nice day!
Thanks, you too!

Footnotes

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

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. ↩

[zzzeek]

hi -

This would be a big deal to change internally as the "change of server default" is a single operation that's bundled with everything else; in the case of PostgreSQL, the change of column type is run before the "everything else" intentionally to suit other cases, such as the server default being added to match the new type and probably some other things.

What's being called for here is that the whole system should detect change of server default + change of type at the same time, and then break out the whole operation into steps based on old default / new default. that probably needs to apply to some backends, but not others (probably not MySQL). But automating this seems like it would be bigger than just PostgreSQL. It also might introduce big surprises in behavior for existing setups if there are new "DROP DEFAULT" steps happening that weren't happening previously.

Detecting it also might be fraught with problems, such as if column type were changing from integer to double, a server default of "0" doesn't need to be dropped.

What I'm getting at is that there's a really easy way for you to make this operation work which is what you did, just break it into two steps. @CaselIT any thoughts on what a heuristic here?

[CaselIT]

I'm thinking if maybe just the drop of server default could be made before other alter operations.
I'm thinking of when that would not be desirable? Maybe while also making a column nullable? But then again the db would not break even if you remove the default of a not null column.

Maybe the code could be altered to:

• if the code wants to drop a server default do that as the first step
• if not do that as now

But I'm honestly not sure if such a change could be done in a retro-compatible way.