Hi all,
I recently ran into an issue where one of our `sqlalchemy-migrate`
downgrade scripts was failing because it was trying to drop an index twice.
The root cause appears to be that SQLAlchemy's `idx.drop()` call does not
update the `indexes` set for the associated table.
I've attached a test case which hopefully explains the situation a little
better.
My question is: is this expected behavior? Given that `index.create`
mutates `table.indexes`, should we expect `index.drop` to do the inverse
and remove it?
My naive assumption that it should, but if that wrong, I'd love to hear why.
Thanks!
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
import sqlalchemy as sa
def test_drop_index():
"""Dropping an index does not remove its reference from associated tables.
The real-world breakage occurs when you combine this behavior w/
sqlalchemy-migrate's `drop_column`:
idx_on_colA.drop()
tableX.drop_column('colA') # <- This will attempt to DROP INDEX again!
The problem is that when sqlalchemy-migrate goes to delete the column, it
erronoeously still sees the index present on `colA` and so attempts to
re-drop the index before dropping the column. Since the index from the
DB's perspective is no longer present, we get an error.
The workaround we have is:
idx_on_colA.drop()
tableX.indexes.remove(idx_on_colA)
See:
https://review.openstack.org/#/c/22628/1/nova/db/sqlalchemy/migrate_repo/versions/144_add_node_to_migrations.py,unified
Versions affected:
This affects 0.8.0b2 and earlier.
"""
engine = sa.create_engine('sqlite:///')
engine.echo = True
meta = sa.MetaData(engine)
users = sa.Table('users', meta,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('email', sa.Unicode))
assert len(users.indexes) == 0
email_idx = sa.Index('idx_users_email', users.c.email)
meta.create_all()
assert len(users.indexes) == 1
email_idx.drop()
# This fails, sinces `users` retains a ref to `email_idx`
assert len(users.indexes) == 0, 'email_idx still present'
if __name__ == '__main__':
test_drop_index()
