Autogenerate does not support sort options on indexes

[jarviliam]

Thank you for all the work you do on sqlalchemy and alembic 🙏

Describe the bug
This is how the index looks like

    __table_args__ = (
        Index(
            "ix_transfers_tenant_id_created_at",
            "tenant_id",
            desc("created_at"),
            unique=False,
            postgresql_concurrently=True,
        ),
    )

Running alembic check reports:

ERROR: New upgrade operations detected: [('remove_index', Index('ix_transfers_tenant_id_created_at', Column('tenant_id', VARCHAR(), table=<transfers>, nullable=False), Column('created_at', TIMESTAMP(timezone=True), table=<transfers>, nullable=False, server_default=DefaultClause(<sqlalchemy.sql.elements.TextClause object at 0x10718e9d0>, for_update=False)))), ('add_index', Index('ix_transfers_tenant_id_created_at', Column('tenant_id', String(), table=<transfers>, nullable=False), <sqlalchemy.sql.elements.UnaryExpression object at 0x1071e9450>))]

Printing out the actual index signature from the comparison gives:

('tenant_id', 'created_at desc')
('tenant_id', 'created_at')

Expected behavior
To factor in index ordering in the autogenerate index detection.

Versions.

• OS: MacOS
• Python: 3.11
• Alembic: 1.10.2
• SQLAlchemy: 2.0.7
• Database: Postgresql 13.8
• DBAPI: asyncpg

Additional context
I figured since alembic can generate a multicolumn index with ordering it would compare the indexes properly. Though I don't really see any documentation on the site so it might just be the way I'm generating it is not correct.

I can get around the detection for now by just omitting the ordering in the table definition after applying the migration.

Have a nice day!

[CaselIT]

Hi,

Thanks for reporting. I think index order is supported, but I'll try looking if we have problem there.

[CaselIT]

Using desc(column('created_at')) is a workaround for now.

Using either desc('created_at') or desc(literal_column('created_at')) has the above issue instead

[CaselIT]

This is a regression of 5c71fd1, since desc('created_at') and desc(literal_column('created_at')) are considered expression indexes while desc(column('created_at')) is not.

@zzzeek it seems that alembic does not detect changes of order at all, I'll restore that behavior also for pg, but maybe we could add support for it?

[CaselIT]

I'll restore that behavior also for pg,

seems easier just to support order in the index

[CaselIT]

the fact that the default sorting is not reported is a bit annoying, but I should have this working.

@zzzeek I think 1.10.3 can be released without this issue, we can target 1.11 for this

[sqla-tester]

CaselIT has proposed a fix for this issue in the main branch:

Use column sort in index compare on postgresql https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/4550