Autogenerate does not support Operator classes for postgres

[tl24]

Describe the bug
For data types that support Operator classes in postgres, such as jsonb and jsonb_path_ops, alembic does not load the operator class from the database during autogenerate detection.

Table definition:

class AuditEvent(Base):
    __tablename__ = "auditevent"
    id: Mapped[UUID] = mapped_column(postgresql.UUID(as_uuid=True), primary_key=True)
    document: Mapped[Optional[dict]] = mapped_column(postgresql.JSONB)
    __table_args__ = (
         Index(
                "ix_auditevent_identifiers",
                text("(document->'identifier') jsonb_path_ops" ),
                postgresql_using="gin",
         )
   )

Running autogenerate logs this:

INFO  [alembic.autogenerate.compare] Detected changed index 'ix_auditevent_identifiers' on 'auditevent': expression ('INDEX', 'document -> identifier') to ('INDEX', '(document->identifier)       jsonb_path_ops')

Expected behavior
Alembic should not detect a change in this index since it is already in the database correctly.

Versions.

• OS: Ubuntu 20.04
• Python: 3.9
• Alembic: 1.12.0
• SQLAlchemy: 2.0.22
• Database: Postgres 13.7
• DBAPI: psycopg2

Additional context
In SQL alchemy 1.x we used to just get warnings that it couldn't read functional indexes. Now in 2.0 it tries to re-create the index without the operator classes (jsonb_path_ops)

Have a nice day!

[zzzeek]

@CaselIT this is more expression based indexes not comparing correctly.

I think we need an option to flat out turn off PG index comparison and I think it has to be off by default. what do you think?

[CaselIT]

The fact that operation class is not detected like that is the expected behavior, since operator classes should be specified as documented here https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#operator-classes

that said this the detection will be skipped for json based indexes so we can keep it open for that fix

[tl24]

I can give the postgresql_ops a try and see what that gives me

[tl24]

Using postgresql_ops generates the index correctly the first time. However immediately rerunning another autogenerate after applying the first migration detects a difference on jsonb_path_ops and generates another change.

This is my updated table/index definition:

class AuditEvent(Base):
    __tablename__ = "auditevent"
    id: Mapped[UUID] = mapped_column(postgresql.UUID(as_uuid=True), primary_key=True)
    document: Mapped[Optional[dict]] = mapped_column(postgresql.JSONB)
    __table_args__ = (
         Index(
                "ix_auditevent_identifiers",
                cls.document["identifier"].label("identifier"),
                postgresql_using="gin",
                postgresql_ops={"identifier": "jsonb_path_ops"},
         )
   )

[CaselIT]

yes, that's the bug in the detection that I mentioned. Can you post the updated info log on alembic?

[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