Postgres Index with opperators dont autogenerate

[garyvdm]

Describe the bug I'm using a postgresql index with a inet_ops operator. Autogenerated code does not include the index.

Expected behavior Autogenerated code should include the index.

To Reproduce

env.py

from logging.config import fileConfig

from sqlalchemy import Column, Index, MetaData, Table, engine_from_config, literal_column
from sqlalchemy.dialects.postgresql import INET
from sqlalchemy import pool

from alembic import context

config = context.config
if config.config_file_name is not None:
    fileConfig(config.config_file_name)

target_metadata = MetaData()

Table(
    "t", target_metadata,
    Column("addr", INET),
    Index(
        "ix_1",
        literal_column("addr inet_ops"),
        postgresql_using="GiST",
    ),
)

def run_migrations_offline() -> None:
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url, target_metadata=target_metadata, 
        literal_binds=True, dialect_opts={"paramstyle": "named"}
    )
    with context.begin_transaction():
        context.run_migrations()

def run_migrations_online() -> None:
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.", poolclass=pool.NullPool,
    )
    with connectable.connect() as connection:
        context.configure(connection=connection, target_metadata=target_metadata)
        with context.begin_transaction():
            context.run_migrations()

if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

Error

% alembic revision --autogenerate -m "Add inet ops table/index"
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 't'
/home/gary/dev/alembic-inet-ops-mcve/ve/lib64/python3.10/site-packages/alembic/ddl/postgresql.py:252: UserWarning: autogenerate skipping functional index ix_1; not supported by SQLAlchemy reflection
  util.warn(
  Generating /home/gary/dev/alembic-inet-ops-mcve/alembic/versions/7361d9f63fd9_add_inet_ops_table_index.py ...  done

Versions.

• OS: Fedora 36
• Python: Python 3.10.7
• Alembic: 1.8.1
• SQLAlchemy: 1.4.41
• Database: postgresql
• DBAPI: psycopg2-binary==2.9.4

Have a nice day! P.S. pull request to follow shortly.

[CaselIT]

Hi,

This is a known limitation. The issue is similar to https://github.com/sqlalchemy/alembic/issues/523 but for postgresql.

In v2 sqlalchemy implemented https://github.com/sqlalchemy/sqlalchemy/issues/7442 and will correctly reflect functional indexes, but currently alembic does not take them into consideration in auto-generate.

[ericvanular]

Since SQLAlchemy 2 supports this, is there a proposed timeline for alembic autogenerate to enable functional indices?

[zzzeek]

there is no proposed timeline, overall as a volunteer driven open source project we don't usually have timelines for things.

[ericvanular]

That's fair, I appreciate all of your work on this ecosystem. Just trying to understand whether I should wait or look for an alternative option in the meantime