Postgresql get_indexes() is erroneously returning EXCLUDE constraints, look to duplicates_index flag as an indicator to skip

[sqlalchemy-bot]

Migrated issue, originally created by Fredrik Blomqvist (@fgblomqvist)

When autogenerating migrations alembic wants to drop the excludeconstraint from my DB.

INFO  [alembic.autogenerate.compare] Detected removed index 'quarters_period_excl' on 'quarters'

The database in use is PostgreSQL 9.6.5.
The ExcludeConstraint is defined in the table_args at the end of the model, and it is created when doing SQLAlchemy create_all.

The import:

from sqlalchemy.dialects.postgresql import ExcludeConstraint

The table args:

__table_args__ = ( 
    ExcludeConstraint(('period', '&&'), name='quarters_period_excl'), 
    db.UniqueConstraint('year', 'name', 'university_id'), 
    db.CheckConstraint(name.in_(['Fall', 'Winter', 'Spring', 'Summer'])) )

This is the code alembic generates:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('quarters_period_excl', table_name='quarters')
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_index('quarters_period_excl', 'quarters', ['period'], unique=False)
    # ### end Alembic commands ###

If this does not ring a bell, I can try to put together an MCV example.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

this is an enhancement as there are many things autogenerate does not detect (See the last paragraph where constraint types such as EXCLUDE are mentioned).

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• removed labels: bug
• added labels: feature

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: autogenerate - detection

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed title from "Autogenerate unable to detect ExcludeConstraint in" to "support autogenerate of EXCLUDE constraints"

[sqlalchemy-bot]

Fredrik Blomqvist (@fgblomqvist) wrote:

Okay that makes sense. However, shouldn't alembic at least just ignore them for now? As in, if it finds an exclude constraint in the DB, just do nothing (I assume that would be a lot less code to write than the actual proper detection)?

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

it's total news to me that postgresql.get_indexes() is finding EXCLUDE constraints. See https://bitbucket.org/zzzeek/sqlalchemy/issues/4122/postgresql-get_indexes-is-returning-a

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed title from "support autogenerate of EXCLUDE constraints" to "Postgresql get_indexes() is erroneously returning "

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• set milestone to "fasttrack"

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• removed labels: feature
• added labels: bug

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

OK SQLAlchemy is doing the right thing here, fix for Alembic is coming through in https://gerrit.sqlalchemy.org/587

[sqlalchemy-bot]

Fredrik Blomqvist (@fgblomqvist) wrote:

Great, thanks!

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Resolve Postgresql implicit indexes via duplicates_constraint

Fixed bug where autogenerate would produce a DROP statement for the index
implicitly created by a Postgresql EXCLUDE constraint, rather than skipping
it as is the case for indexes implicitly generated by unique constraints.
Makes use of SQLAlchemy 1.0.x's improved "duplicates index" metadata and
requires at least SQLAlchemy version 1.0.x to function correctly.

Change-Id: I7362c8045f69553c6090dd3cb236569b0c9b1e67
Fixes: #461

→ 7426729

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed