Wrong index detection on cockroachdb

[OndrejIT]

Hi, I'm trying to make a migration on my cockroachdb DB + sqlmodel.
when I first init alembic and create DB everything works, if I scan again (alembic revision --autogenerate) for possible changes, alembic prints "Detected changed index 'ix_file_bucket' on 'file': expression ('INDEX', 'bucket nulls first') to ('INDEX', 'bucket')" (I didn't change anything in code and database)
and alembic creates the following migrations:

op.drop_index(op.f('ix_file_bucket'), table_name='file')
op.create_index('ix_file_bucket', 'file', [sa.text('bucket NULLS FIRST')], unique=False, postgresql_using='prefix')

the next time the alembic is triggered, it fails.
with the message:
sqlalchemy.exc.DBAPIError: (sqlalchemy.dialects.postgresql.asyncpg.Error) <class 'asyncpg.exceptions.ObjectNotInPrerequisiteStateError'>: index "ix_file_bucket" is cancelled, try again later

is there any way to solve this? currently I have to manually delete bad migrations line every time.

Versions.

• OS: Docker
• Python: docker.io/python:3.12-alpine
• Alembic: 1.12.1
• SQLAlchemy: 1.4.41 + sqlmodel==0.0.11
• Database: cockroachdb v23.1.11
• DBAPI: sqlalchemy-cockroachdb==2.0.1

[zzzeek]

this is some issue with both index reflection and something with the driver itself that is out of scope for alembic, that error message is unknown to us here so you'd want to ask on the sqlalchemy-cockroachDB driver tracker, cc @gordthompson

[CaselIT]

is there any way to solve this? currently I have to manually delete bad migrations line every time.

this is similar to the other issue with expression reflection. you are placing order modifying in the expression.
Instead of sa.text('bucket NULLS FIRST') use bucket.nulls_first() or sa.column('bucket').nulls_first() or sa.nulls_first(sa.text('bucket')) in the index definition

[gordthompson]

Alembic is doing that text() thing. When I tested it with model

class Account(Base):
    __tablename__ = "account"
    account_number = Column(Integer, primary_key=True)
    customer_name = Column(String(50))
    invoices = relationship("Invoice", back_populates="account")
    Index("ix_account_customer_name", customer_name.nullsfirst())

running

alembic revision --autogenerate -m "Added account and invoice tables"

produced a revision file that included

op.create_index('ix_account_customer_name', 'account', [sa.text('customer_name NULLS FIRST')], unique=False)

That said, I am unable to reproduce the issue against CockroachDB using psycopg2, and if I try using asyncpg then Alembic doesn't like it at all:

sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/14/xd2s)  
sys:1: RuntimeWarning: coroutine 'connect' was never awaited

[sqla-tester]

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

More PostgreSQL expression index compare fixes https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/4912