literal_column() in ExcludeConstraint are autogenerated as column(), failing in the migration

[jankatins]

Describe the bug

If you add a literal_column in a ExcludeConstraint (e.g. a literal_column("int8range(col_from, col_to)")), this is rendered as column("int8range(col_from, col_to)") in the autogenerated migration and the migration will fail as such a column does not exist in the table.

Expected behavior

literal_columns are passed through in the rendered migration

To Reproduce

from sqlalchemy.dialects.postgresql import JSONB, ExcludeConstraint
from sqlalchemy.sql.expression import column, literal_column
from sqlalchemy import Column, Table
from sqlalchemy.orm import declarative_base
from sqlalchemy.sql.schema import MetaData
from sqlalchemy.sql.sqltypes import *

Base2 = declarative_base()
whatever2 = Table(
    "whatever2",
    Base2.metadata,
    Column("id", BigInteger(), nullable=False, primary_key=True),
    ExcludeConstraint(
        ("id", "="),
        (literal_column("id + 2"), "="),
        name=f"whatever_id_int8range_excl",
    ),
)

Error

The migration code after alembic revision --autogenerate -m "test2"looks like this:

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('whatever2',
    sa.Column('id', sa.BigInteger(), nullable=False),
    postgresql.ExcludeConstraint((sa.column('id'), '='), (sa.column('id + 2'), '='), using='gist', name='whatever_id_int8range_excl'),
    sa.PrimaryKeyConstraint('id')
    )

running this migration via alembic upgrade head results in:

psycopg2.errors.UndefinedColumn: column "id + 2" named in key does not exist


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

Traceback (most recent call last):
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/bin/alembic", line 8, in <module>
    sys.exit(main())
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/alembic/config.py", line 591, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/alembic/config.py", line 585, in main
    self.run_cmd(cfg, options)
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/alembic/config.py", line 562, in run_cmd
    fn(
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/alembic/command.py", line 378, in upgrade
    script.run_env()
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/alembic/script/base.py", line 569, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
    module = load_module_py(module_id, path)
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 110, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 883, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/Users/jankatins/projects/50hz/mds-elt-service/alembic/env.py", line 115, in <module>
    run_migrations_online()
  File "/Users/jankatins/projects/50hz/mds-elt-service/alembic/env.py", line 109, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/alembic/runtime/environment.py", line 867, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/alembic/runtime/migration.py", line 624, in run_migrations
    step.migration_fn(**kw)
  File "/Users/jankatins/projects/50hz/mds-elt-service/alembic/versions/f7aae215babc_test2.py", line 30, in upgrade
    op.create_table('whatever2',
  File "<string>", line 8, in create_table
  File "<string>", line 3, in create_table
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/alembic/operations/ops.py", line 1260, in create_table
    return operations.invoke(op)
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/alembic/operations/base.py", line 401, in invoke
    return fn(self, operation)
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/alembic/operations/toimpl.py", line 114, in create_table
    operations.impl.create_table(table)
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/alembic/ddl/impl.py", line 354, in create_table
    self._exec(schema.CreateTable(table))
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/alembic/ddl/impl.py", line 193, in _exec
    return conn.execute(  # type: ignore[call-overload]
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1380, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
    return connection._execute_ddl(
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1472, in _execute_ddl
    ret = self._execute_context(
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
    self._handle_dbapi_exception(
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/Users/jankatins/Library/Caches/pypoetry/virtualenvs/mds-elt-service-JeB-l5pZ-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column "id + 2" named in key does not exist

[SQL:
CREATE TABLE whatever2 (
        id BIGSERIAL NOT NULL,
        PRIMARY KEY (id),
        CONSTRAINT whatever_id_int8range_excl EXCLUDE USING gist (id WITH =, "id + 2" WITH =)
)

]
(Background on this error at: https://sqlalche.me/e/14/f405)

Versions.

• OS: Macosx latest
• Python: 3.10
• Alembic: alembic==1.9.4
• SQLAlchemy: SQLAlchemy==1.4.46
• Database: postgresql 14
• DBAPI: psycopg2-binary==2.9.5

[jankatins]

Suggested fix is in #1185

[CaselIT]

Hi,

thanks for reporting. That looks indeed like a bug.
does passing text() works in this case?
A string is interpreted as a column by alembic and that may actually be wrong, at least according to the docs at sqlalchemy https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#sqlalchemy.dialects.postgresql.ExcludeConstraint.params.*elements "or a raw SQL string". Looking at the implementation in sqlalchemy I think the assumption there is also that strings are columns, so the docs may be improved. (trying to use ExcludeConstraint(("id + 2", "=")) does indeed error)

[jankatins]

Adding text() around it makes it even worse:

- postgresql.ExcludeConstraint((sa.column('branch_id'), '='), (sa.column('int8range(commit_id_from, commit_id_to)'), '&&'), (sa.column('mrid'), '='), using='gist', name='whatever_branch_id_int8range_mrid_excl'),
+ postgresql.ExcludeConstraint((sa.column('branch_id'), '='), (sa.column('mrid'), '='), using='gist', name='whatever_branch_id_int8range_mrid_excl'),

-> The whole element is removed :-(

[jankatins]

If I remember correctly, that was due to coercions.expect_col_expression_collection() not returning something in the add_element position here:

https://github.com/sqlalchemy/sqlalchemy/blob/5147bf8e3cb321b8728b3af5bb0b2644995b3793/lib/sqlalchemy/dialects/postgresql/ext.py#L218-L225

        for (expr, column, strname, add_element), operator in zip(
            coercions.expect_col_expression_collection(
                roles.DDLConstraintColumnRole, expressions
            ),
            operators,
        ):
            if add_element is not None:
                columns.append(add_element)

[CaselIT]

I'll take a look at how sqlalchemy behaves with text.

@zzzeek do you think text should be supported?

[jankatins]

One thing which should IMO be changed is that ExcludeConstraint.__init__() errors when an expression which got passed in is not actually applied.

E.g.

if add_element is not None:
    columns.append(add_element)
else:
    RuntimeError(...)

debugging that part was a real pain: I tried using functions func.int8range("col", "col") instead of literal_column first and that simply vanished like text(). And adding columns within the func func.int8range(column("col"), column("col")) was ending up as a single column without the int8range around it (reason: in that generator there is a visitor going into the func, pulling out the two columns and only using the first)...

[CaselIT]

That's something that indeed should be improved.
That issue with only a single column being considered is somewhat similar to this sqlalchemy/sqlalchemy#9233 (I haven't checked to see if it's the same function that gets called, but it may very well be)

let's wait for mike's opinion on these other things.

currently on sqlalchemy we can improve the documentation indicating that ExcludeConstrain does not accpect an SQL string but the name of a column as string

[zzzeek]

I'll take a look at how sqlalchemy behaves with text.

@zzzeek do you think text should be supported?

I would look to see what rendering for Index and CheckConstraint do. Whatever is implemented for ExcludeConstraint should work for those as well otherwise we are being inconsistent.