Autogenerate produces no migration when a Computed column expression changes (PostgreSQL)

[fabiob]

Describe the bug

When a Computed column expression is changed in the SQLAlchemy model and alembic revision --autogenerate is run, Alembic emits no migration at all. The built-in _compare_computed_default comparator detects the expression difference, emits a UserWarning: Computed default on <table>.<column> cannot be modified, and returns STOP — but it never sets alter_column_op.modify_server_default. As a result, has_changes() returns False and no AlterColumnOp is emitted. The rewriter (if any) never gets a chance to run.

This was acknowledged as a deliberate limitation in #624, where @zzzeek stated:

the next level is support of changes in "computed", which has to do with reading the table columns in the DB and reading them in the model and comparing, and I'm assuming this is what you're referring towards. we don't implement this feature for CHECK constraints either right now

and @CaselIT confirmed:

I'll omit the support for changes for now

The result is that users have no autogenerate path for computed column expression changes on PostgreSQL. The warning fires, but nothing is emitted — the user must hand-write the migration.

Expected behavior

When a Computed column expression changes, autogenerate should emit a migration that drops and re-adds the column. PostgreSQL does not support ALTER COLUMN on a generated/computed column — the column must be dropped and re-added. This is the correct DDL:

ALTER TABLE products DROP COLUMN total;
ALTER TABLE products ADD COLUMN total INTEGER GENERATED ALWAYS AS (price * quantity + tax) STORED NOT NULL;

The downgrade should reverse this: drop the new column and re-add the old one with the previous computed expression.

To Reproduce

import sqlalchemy as sa
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class Product(Base):
    __tablename__ = "products"

    id: Mapped[int] = mapped_column(sa.Integer, primary_key=True)
    price: Mapped[int] = mapped_column(sa.Integer, nullable=False)
    quantity: Mapped[int] = mapped_column(sa.Integer, nullable=False)

    # First migration: create the table with this computed column
    total: Mapped[int] = mapped_column(
        sa.Integer(),
        sa.Computed("price * quantity", persisted=True),
        nullable=False,
    )

1. Create the first migration and apply it.
2. Change the Computed expression from "price * quantity" to "price * quantity + tax" (or any other change to the expression).
3. Run alembic revision --autogenerate.

Error

.../alembic/autogenerate/compare/server_defaults.py:114: UserWarning: Computed default on products.total cannot be modified
  util.warn("Computed default on %s.%s cannot be modified" % (tname, cname))
INFO  [alembic.env] No changes in schema detected.

No migration file is generated. The computed expression change is silently ignored.

Root cause

The flow through the comparator dispatch (with compare_server_default=True):

1. _user_compare_server_default (FIRST priority): sets alter_column_op.existing_server_default to the old Computed (the DB state), then returns CONTINUE (because compare_server_default=True is not callable).

2. _compare_computed_default (default/MEDIUM priority): detects the expression difference via _normalize_computed_default, calls _warn_computed_not_supported, and returns STOP — but never sets alter_column_op.modify_server_default.

3. has_changes() returns False because modify_server_default is still False (the sentinel). No AlterColumnOp is emitted.

The key code in _compare_computed_default (alembic/autogenerate/compare/server_defaults.py):

if rendered_metadata_default != rendered_conn_default:
    _warn_computed_not_supported(tname, cname)

return PriorityDispatchResult.STOP   # STOP, but modify_server_default never set

Workaround we are currently using

We worked around this in our env.py with a custom comparator + rewriter. The comparator runs at FIRST priority (before _compare_computed_default) and actually sets modify_server_default when the expression differs. The rewriter then converts the resulting AlterColumnOp into a DropColumnOp + AddColumnOp pair, with DropColumnOp._reverse wired so the downgrade re-adds the old computed column.

from alembic.autogenerate import rewriter
from alembic.autogenerate.compare import comparators
from alembic.operations import ops
from alembic.util import DispatchPriority, PriorityDispatchResult
from sqlalchemy import Column
from sqlalchemy.sql.schema import Computed
from typing import Any, cast


writer = rewriter.Rewriter()


def _column_from_op(op: ops.AlterColumnOp, *, use_existing: bool) -> Column[Any]:
    """Reconstruct a Column from an AlterColumnOp.

    use_existing=True  -> existing_* attributes (current DB state, for downgrade)
    use_existing=False -> modify_* with fallback to existing_* (target state, for upgrade)
    """
    if use_existing:
        col_type = op.existing_type
        col_default = op.existing_server_default
        col_nullable = op.existing_nullable
    else:
        col_type = op.modify_type if op.modify_type is not None else op.existing_type
        col_default = (
            op.modify_server_default
            if op.modify_server_default is not False
            else op.existing_server_default
        )
        col_nullable = (
            op.modify_nullable if op.modify_nullable is not None else op.existing_nullable
        )

    return Column(
        op.column_name,
        col_type,
        server_default=cast("Column[Any] | None", col_default),
        nullable=col_nullable if col_nullable is not None else True,
    )


@comparators.dispatch_for("column", priority=DispatchPriority.FIRST, qualifier="postgresql")
def _compare_computed_expression(
    autogen_context: Any,
    alter_column_op: ops.AlterColumnOp,
    schema: str | None,
    tname: str,
    cname: str,
    conn_col: Any,
    metadata_col: Any,
) -> PriorityDispatchResult:
    """Detect computed-expression changes and set modify_server_default.

    Runs at FIRST priority, before Alembic's _compare_computed_default (which
    only warns and returns STOP without setting modify_server_default).  When the
    computed expression differs, we set modify_server_default to the new Computed
    so that has_changes() returns True and an AlterColumnOp is emitted — which the
    rewriter then converts to drop + add.
    """
    metadata_default = metadata_col.server_default
    conn_default = conn_col.server_default

    if not isinstance(metadata_default, Computed):
        return PriorityDispatchResult.CONTINUE

    alter_column_op.existing_server_default = conn_default

    if isinstance(conn_default, Computed):
        rendered_metadata = str(
            metadata_default.sqltext.compile(
                dialect=autogen_context.dialect,
                compile_kwargs={"literal_binds": True},
            ),
        )
        rendered_conn = str(
            conn_default.sqltext.compile(
                dialect=autogen_context.dialect,
                compile_kwargs={"literal_binds": True},
            ),
        )
        if rendered_metadata == rendered_conn:
            return PriorityDispatchResult.CONTINUE
    elif conn_default is None:
        pass  # Column was not computed before but is now — treat as a change.
    else:
        return PriorityDispatchResult.CONTINUE

    alter_column_op.modify_server_default = metadata_default
    return PriorityDispatchResult.STOP


@writer.rewrites(ops.AlterColumnOp)
def drop_and_recreate_column(context, revision, op: ops.AlterColumnOp):
    """Convert AlterColumnOp on a computed column into drop + add.

    The upgrade drops the old column and adds the new one (with the new computed
    expression).  The downgrade reverses this: drops the new column and re-adds the
    old one, via DropColumnOp._reverse.
    """
    if not any(
        isinstance(sd, Computed)
        for sd in (op.existing_server_default, op.modify_server_default)
    ):
        return op

    new_column = _column_from_op(op, use_existing=False)
    old_column = _column_from_op(op, use_existing=True)

    drop_op = ops.DropColumnOp(op.table_name, op.column_name, schema=op.schema)
    drop_op._reverse = ops.AddColumnOp(op.table_name, old_column, schema=op.schema)

    add_op = ops.AddColumnOp(op.table_name, new_column, schema=op.schema)

    return [drop_op, add_op]

This produces the expected migration:

Upgrade:

op.drop_column('products', 'total')
op.add_column('products', sa.Column('total', sa.Integer(),
    server_default=sa.Computed('price * quantity + tax', persisted=True),
    nullable=False))

Downgrade:

op.drop_column('products', 'total')
op.add_column('products', sa.Column('total', sa.Integer(),
    server_default=sa.Computed('price * quantity', persisted=True),
    nullable=False))

Known limitations of the workaround

1. Indexes are not automatically recreated. If the computed column has index=True, dropping the column drops the index. Alembic's autogenerate will detect the missing index as a separate diff and emit a CreateIndexOp — this works, but the index recreation is a separate op, not part of the drop+add pair.

2. DropColumnOp._reverse is a private attribute. Wiring the downgrade via _reverse is the mechanism Alembic uses internally (see DropColumnOp.reverse()), but accessing it directly is not part of the public API.

3. Dialect-specific. The comparator is registered with qualifier="postgresql". SQL Server and MySQL/MariaDB also cannot ALTER COLUMN on computed columns, but the drop/recreate semantics may differ (e.g. SQL Server requires dropping indexes that reference the column first — see ariga/atlas#3595).

Related issues

• Computed column migration file generated incorrectly #624 — Original issue that added Computed column rendering support. @zzzeek noted that "the next level is support of changes in computed" was not implemented, and @CaselIT confirmed "I'll omit the support for changes for now".
• Consider Removing New Line Characters when Comparing Computed Constructs #1121 — _normalize_computed_default doesn't strip newlines, causing false-positive warnings (open, labeled bug).
• Comparing computed fields throws warn #1151 — False-positive "cannot be modified" warnings due to PostgreSQL reformatting the expression on reflection (closed as not planned).
• Unexpected warning for computed column when migrating with MariaDB #1607 — Same warning on MariaDB due to TRUE vs 1 normalization (open, labeled autogenerate - detection).
• Computed statements containing newlines cause warnings on autogenerate #1391 — Computed statements containing newlines cause warnings.

Versions

• OS: NixOS
• Python: 3.14
• Alembic: 1.17.0
• SQLAlchemy: 2.0.44
• Database: PostgreSQL
• DBAPI: asyncpg

Additional context

I'm open to contributing a PR if the maintainers are interested. I appreciate guidance on the preferred architecture:

• Should the comparator + rewriter be integrated into the PostgreSQL dialect impl (alembic/ddl/postgresql.py), or kept as a globally-registered comparator via comparators.dispatch_for?
• Is there a cleaner public API for wiring the downgrade reverse than setting DropColumnOp._reverse directly?
• Should index drop/recreate be handled as part of the same op, or left as a separate autogenerate diff (current behavior)?

AI assistance disclosure

I've been using Alembic for over five years, but the analysis of Alembic's internal comparator dispatch, the root cause investigation, and the workaround code were assisted by open-source AI tooling (OpenCode with the GLM-5.2 model). I reviewed and tested everything before posting, but I want to be transparent about the process.

Have a nice day

[CaselIT]

Hi,

When a Computed column expression changes, autogenerate should emit a migration that drops and re-adds the column. PostgreSQL does not support ALTER COLUMN on a generated/computed column — the column must be dropped and re-added. This is the correct DDL:

ALTER TABLE products DROP COLUMN total;
ALTER TABLE products ADD COLUMN total INTEGER GENERATED ALWAYS AS (price * quantity + tax) STORED NOT NULL;

The downgrade should reverse this: drop the new column and re-add the old one with the previous computed expression.

This is not safe to do, since the column could be used in indexes, check constraints, fk, etc.

So I think autogenerate does the right thing and doesn't produce possibly unsafe migrations.

The fix here is to ask the db vendors to add an alter that can change the generated column

Closing for now

[CaselIT]

On second thought, moving to discussion, sinche with the new extensible architecture of autogenerate this could be a cookbook example or an optional behavior

[CaselIT]

apparently github forgot how to convert an issue to discussion. it's currently erroring out. will try again later

[sgsabbage]

I won't attempt to convert this into a discussion (not even sure I can!), but just a note on this to add that in PG 18 they added the ability to alter a Generated column's expression with SET EXPRESSION AS, ref: https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DESC-SET-EXPRESSION

Unsure if this is useful at this time, but as I stumbled across it the other day thought it might be helpful to record here.

[CaselIT]

Oh, interesting. Let's keep it here as issue then to see if we can improve things in the future

[CaselIT]

for PG:

• it's from version 17 https://www.postgresql.org/docs/17/sql-altertable.html#SQL-ALTERTABLE-DESC-SET-EXPRESSION
• previous version did support dropping a generated expression, but seemingly not adding one.