Gin index on JSONB specific key not detected with autogenerate

[sqlalchemy-bot]

Migrated issue, originally created by Pierre Kephas (@kephas)

Hello,

I have a table with a JSONB column :

meta = sa.Column('metadata', sa.ext.mutable.MutableDict.as_mutable(sa.dialects.postgresql.JSONB),
                     nullable=True, server_default='{}')

And I want a GIN index on the some_key key (see https://www.postgresql.org/docs/current/static/datatype-json.html#JSON-INDEXING if you do not know a lot about GIN indexes).

I declared :

__table_args__ = (
    sa.Index(
        'ix_table_metadata_some_key',
        sa.text('''(metadata->'some_key')'''),
        postgresql_using='gin',
    )
)

Then, running alembic revision -m "Some message" --autogenerate generates an empty migration.

Note: creating a GIN index on meta column works fine.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

you will note the warning:

alembic/util/messaging.py:69: UserWarning: autogenerate skipping functional index ix_table_metadata_some_key; not supported by SQLAlchemy reflection

that is, autogenerate has no way to know if this index already exists in the database. This behavior was added to #282.

You'll need to add this index to your migration script manually.

[sqlalchemy-bot]

Pierre Kephas (@kephas) wrote:

Hi,

Thanks for your answer.

Yes, I noted the warning, but I forgot to mention it (sorry).

Well, I guess that detecting functional indexes is not easy... But anyway, I already added to my migration script manually. :)

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

sorry!

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed