Issues

Issue #157 resolved

Autogenerate detected removed index, for constraints

John Kida
created an issue

(Postgresql) It appears everywhere I have a constraint in my sqlalchemy code in which there is a unique constaint index alembic --autogenerate wants to remove it.

ie. python code: lead_id = Column(Integer, ForeignKey('crm_lead.id'), unique=True)

DataBase \d: Indexes: "chat_pkey" PRIMARY KEY, btree (id) "chat_lead_id_key" UNIQUE CONSTRAINT, btree (lead_id)

INFO [alembic.autogenerate.compare] Detected removed index 'chat_lead_id_key' on 'chat'

It appears alembic does not consider constraints as indexes, and psql does.

Comments (31)

  1. Mike Bayer repo owner

    we tried really hard to get this right and there is a significant, explicit effort to make sure PG's uniques/indexes line up, including tests for this specific pattern (see https://bitbucket.org/zzzeek/alembic/src/85a21ed6cc637efc2e9705a30d397a194acb9742/tests/test_autogenerate.py?at=master#cl-818, https://bitbucket.org/zzzeek/alembic/src/85a21ed6cc637efc2e9705a30d397a194acb9742/tests/test_autogenerate.py?at=master#cl-911).

    so knowing what PG version and what SQLAlchemy version here (assuming you're on the latest Alembic) would be helpful.

  2. Mike Bayer repo owner

    I'm testing over here and I'm not seeing it. Using a model like this:

    from sqlalchemy import MetaData, Table, Column, Integer, String, ForeignKey
    
    target_metadata = MetaData()
    
    Table('t', target_metadata, Column('id', Integer, primary_key=True))
    
    Table('t2', target_metadata, Column('id', Integer, primary_key=True),
                Column('t1id', Integer, ForeignKey('t.id'), unique=True))
    

    I run autogenerate for rev 1 and as expected we get this:

    def upgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.create_table('t',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.PrimaryKeyConstraint('id')
        )
        op.create_table('t2',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('t1id', sa.Integer(), nullable=True),
        sa.ForeignKeyConstraint(['t1id'], ['t.id'], ),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('t1id')
        )
        ### end Alembic commands ###
    

    run that upgrade. So now, we have the unique constraints in the DB (which PG sees as indexes) and we have the unique=True in the model, which I also made sure I applied on an FK column as you have above. run autogenerate again, I get:

    def upgrade():
        ### commands auto generated by Alembic - please adjust! ###
        pass
        ### end Alembic commands ###
    

    So need all the data here - PG/SQLA/alembic versions, and also SQL output of what you get when you run alembic revision --autogenerate, at least some portion illustrating how it's coming up with or without indexes, uniques etc.

  3. Mike Bayer repo owner

    just to check, as I notice your constraint appears to be named on the PG side, I gave it a name in my test:

    def upgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.create_table('t',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.PrimaryKeyConstraint('id')
        )
        op.create_table('t2',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('t1id', sa.Integer(), nullable=True),
        sa.ForeignKeyConstraint(['t1id'], ['t.id'], ),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('t1id', name="t1id_uq_key")
        )
        ### end Alembic commands ###
    

    the constraint remains unnamed when I use "unique=True". but a run against that still had the right answer, as we compare the indexes/constraints on the names of the columns within, if either side has no name.

  4. Mike Bayer repo owner

    specifically, if you run this for a particular table with a unique, you should see results for both indexes and unique constraints:

    >>> from sqlalchemy import create_engine, inspect
    >>> e = create_engine("postgresql://scott:tiger@localhost/test")   
    >>> insp = inspect(e)
    >>> insp.get_unique_constraints("t2")
    [{'column_names': [u't1id'], 'name': u't1id_uq_key'}]
    >>> insp.get_indexes("t2")
    [{'unique': True, 'name': u't1id_uq_key', 'column_names': [u't1id']}]
    >>> 
    

    otherwise it is sounding like those constraints were created on the DB side as "unique index" alone - the fact that they are named on the DB side and not in your Python side suggests a mismatch between your model and what's actually in the DB. if you add "index=True" to those columns, then you'll get a unique index without the UNIQUE constraint in the Python model.

    If I run a test like this, e.g. first I create the schema using "unique=True, index=True" on the column. then I remove the "index=True" part, then I run another autogenerate, I get:

    def upgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.create_unique_constraint(None, 't2', ['t1id'])
        op.drop_index('ix_t2_t1id', 't2')
        ### end Alembic commands ###
    

    which in fact is correct - by removing "index=True", it means I no longer want an index, i want just the unique constraint. which PG then creates as a pair of "unique constraint" / "index" as it does, but that's fine. the schema and model are remaining consistent with each other.

    please confirm these are in fact just unique indexes in your PG database and that there's not an actual unique constraint, e.g. that adding index=True resolves, and we can close this, thanks !

  5. John Kida reporter

    I am running SQLAlchemy 0.8.0 , PostgreSQL 9.1.10

    I tried running the test you mentioned, but it looks like i dont have a get_unique_contraints() method in SA 0.8.0

    lead_id = Column(Integer, ForeignKey('crm_lead.id'), unique=True)
    
    In [5]: insp.get_indexes('chat')
    Out[5]: [{'column_names': [u'lead_id'], 'name': u'chat_lead_id_key', 'unique': True}]
    

    PG sees this as type = index, so that looks correct to me.

    This column was always ONLY a unique contraint, it was never a index=True. However, I cant be sure of the naming you mentioned.. Is it possible that maybe. it was named before, when the DB was created, then the name was removed... I believe you tested the oposite, ie. unnammed, created db, then named.

    Im not sure i understand exactly what I should confirm, I believe these are unique constraints.. the DB shows

    Indexes:
        "chat_pkey" PRIMARY KEY, btree (id)
        "chat_lead_id_key" UNIQUE CONSTRAINT, btree (lead_id)
    Foreign-key constraints:
        "chat_lead_id_fkey" FOREIGN KEY (lead_id) REFERENCES crm_lead(id)
    

    They should be just unique constraints, im just not sure why autogenerate is appearing to want to remove them, or thinks it needs to drop an index.

    Sorry if this wasnt helpful i can try to do some testing to reproduce it, from scratch.

  6. Mike Bayer repo owner

    OK the first thing to understand is that when a "UNIQUE CONSTRAINT" is created on postgresql, PG will automatically create an additional UNIQUE INDEX at the same time. So generating a schema with "unique=True" means that PG will report on two constraints - one is "UNIQUE CONSTRAINT" and the other "UNIQUE INDEX". so that's the confusion that the new index comparison feature has to deal with.

    Next, I didn't realize yesterday that we didn't even port the "unique constraint" feature to 0.8 at all, and there's no good reason for that so this is now backported to 0.8.4. So for the moment, your issue should resolve if you try out 0.8.4, which I can release later in the week if needed. you can get current 0.8 at https://bitbucket.org/zzzeek/sqlalchemy/get/rel_0_8.zip - let me know if the issue is resolved if you get the chance to try it out.

  7. John Kida reporter

    Mike, I upgraded to SA rel_0_8 (8.4dev ) using the zip you provided the link to, and it is now recognizing the PG index contraints correctly. Everything seems to be working as expected with the SA upgrade. Is everything still in line to get 8.4 pushed to PyPi this week?

    Thanks for the great work!

  8. Mike Bayer repo owner

    hi john -

    this would normally be very early to push 0.8.4 but since this issue is going to hit everyone using alembic I'll try to get it out by the weekend.

    • mike
  9. rach

    This ticket seem to be focus on Postgres. We met a similar with 0.6.1 issue with one of our project which alas use MySQL. Is 0.8.4 SQLAlchemhy is going to fix the issues on MySQL too?

  10. Zeray Rice

    I have a issue with MySQL that is similar to this issue, so I report it here.

    Before start:

    I'm new to alembic, and I'm not familiar with some concepts in database.

    Library version:

    Python 2.7.5

    Mako==0.9.0
    MarkupSafe==0.18
    MySQL-python==1.2.4
    SQLAlchemy==0.8.4
    alembic==0.6.1
    

    MySQL 5.6.10 Mac OS X 10.9.1

    All codes below runs inside virtualenv and uses InnoDB as database engine.

    Two parts:

    I. Database Structures:

    class User(Base):
        __tablename__ = "user"
        id = Column(Integer, primary_key=True)
        name = Column(String(30), index = True, unique = True) # PROBLEM HERE
    

    Problem:

    When I run alembic auto-generate first time, It seems normal. But when I run it again (just after upgrading the database, nothing changed), it will get op.drop_constraint(u'ix_user_name', 'user'). This would be failed.

    I solved it by removing the index = True (it seems misused.)

    II. Database Structures:

    class User(Base):
        __tablename__ = "user"
        id = Column(Integer, primary_key=True)
        name = Column(String(30), unique = True)
    
    class Thing(Base):
        __tablename__ = "thing"
        id = Column(Integer, primary_key = True)
        owner_id = Column(Integer, ForeignKey('user.id')) # PROBLEM HERE
    

    Problem:

    Like part I, generate it, upgrade it and re-generate it. I got op.drop_index('owner_id', 'thing'). It would cause exception Cannot drop index 'owner_id': needed in a foreign key constraint. Nothing changed except upgrading the database.

  11. Mike Bayer repo owner

    I've identified issues with this logic in the current master due to a related fix, but the issue is not in 0.6.1.

    if you've removed "index=True", that would be why you're getting a drop_constraint() - that flag is why there is an "ix_user_name" in the first place. put the index=True back in the Column. I will add additional tests to ensure no net change is detected.

  12. Mike Bayer repo owner

    confirmed that the dedupe logic which takes place for PG has to be completely reversed for MySQL, which instead of reporting uniques as indexes, reports indexes as uniques. I'm rewriting the whole index autogenerate feature right now.

  13. Mike Bayer repo owner

    did you restore that index=True we talked about? removing that does indicate it should do a drop_index(). I was able to reproduce (and fix) all the symptoms you described.

  14. Mike Bayer repo owner

    just tried it here (not doing the index=True part), cannot reproduce.

    Model #1:

    class User(Base):
        __tablename__ = "user"
        id = Column(Integer, primary_key=True)
        name = Column(String(30), index = True, unique = True)
    

    autogen #1:

    def upgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.create_table('user',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(length=30), nullable=True),
        sa.PrimaryKeyConstraint('id')
        )
        op.create_index('ix_user_name', 'user', ['name'], unique=True)
        ### end Alembic commands ###
    
    
    def downgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.drop_index('ix_user_name', 'user')
        op.drop_table('user')
        ### end Alembic commands ###
    

    model #2:

    class User(Base):
        __tablename__ = "user"
        id = Column(Integer, primary_key=True)
        name = Column(String(30), index = True, unique = True)
    
    
    class Thing(Base):
        __tablename__ = "thing"
        id = Column(Integer, primary_key = True)
        owner_id = Column(Integer, ForeignKey('user.id'))
    

    autogen #2:

    def upgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.create_table('thing',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('owner_id', sa.Integer(), nullable=True),
        sa.ForeignKeyConstraint(['owner_id'], ['user.id'], ),
        sa.PrimaryKeyConstraint('id')
        )
        ### end Alembic commands ###
    
    
    def downgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.drop_table('thing')
        ### end Alembic commands ###
    

    model #3 - no change, just run revision --autogenerate again

    autogen #3:

    def upgrade():
        ### commands auto generated by Alembic - please adjust! ###
        pass
        ### end Alembic commands ###
    
    
    def downgrade():
        ### commands auto generated by Alembic - please adjust! ###
        pass
        ### end Alembic commands ###
    
  15. Alex Kir

    We didn't talk about it, it was someone else :) But i don't have index=True at all, here is my model

    class User(UserMixin, CRUDMixin, db.Model):
        __tablename__ = 'users'
    
        id = db.Column(db.Integer, primary_key=True)
    
        #data
        name = db.Column(db.String(40), unique=True)
        email = db.Column(db.String(30), unique=True)
        _password = db.Column(db.LargeBinary(120))
        _salt = db.Column(db.String(120))
        admin = db.Column(db.Integer, default=0)
        verified = db.Column(db.Boolean, default=False, nullable=False)
    
        # relations
        lals_entries = db.relationship('Entry', backref='owner', lazy='dynamic')
        lals_posts = db.relationship('Lol', backref='comment', lazy='dynamic')
        blog_posts = db.relationship('Blog', backref='author', lazy='dynamic')
    

    And here if I remove (for example, though it happens for any change) nullable=False from verified and runing migration - i'm getting drop_index

  16. Mike Bayer repo owner

    Whoops! OK before I saw your Postgresql note, just tried it on MySQL, no issues.

    Using Postgresql. Model 1:

    class User(Base):
        __tablename__ = 'users'
    
        id = Column(Integer, primary_key=True)
    
        name = Column(String(40), unique=True)
        email = Column(String(30), unique=True)
        _password = Column(LargeBinary(120))
        _salt = Column(String(120))
        admin = Column(Integer, default=0)
        verified = Column(Boolean, default=False, nullable=False)
    

    autogenerate #1:

    def upgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.create_table('users',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(length=40), nullable=True),
        sa.Column('email', sa.String(length=30), nullable=True),
        sa.Column('_password', sa.LargeBinary(length=120), nullable=True),
        sa.Column('_salt', sa.String(length=120), nullable=True),
        sa.Column('admin', sa.Integer(), nullable=True),
        sa.Column('verified', sa.Boolean(), nullable=False),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('email'),
        sa.UniqueConstraint('name')
        )
        op.create_unique_constraint(None, 'users', ['name'])
        op.create_unique_constraint(None, 'users', ['email'])
        ### end Alembic commands ###
    
    
    def downgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.drop_constraint(None, 'users')
        op.drop_constraint(None, 'users')
        op.drop_table('users')
        ### end Alembic commands ###
    

    Model 2 - no change. Autogenerate #2, no changes:

    def upgrade():
        ### commands auto generated by Alembic - please adjust! ###
        pass
        ### end Alembic commands ###
    
    
    def downgrade():
        ### commands auto generated by Alembic - please adjust! ###
        pass
        ### end Alembic commands ###
    

    Model 3 - remove nullable=False from verified:

    class User(Base):
        __tablename__ = 'users'
    
        id = Column(Integer, primary_key=True)
    
        name = Column(String(40), unique=True)
        email = Column(String(30), unique=True)
        _password = Column(LargeBinary(120))
        _salt = Column(String(120))
        admin = Column(Integer, default=0)
        verified = Column(Boolean, default=False)
    

    autogenerate #3: we get just the column that was changed, no drop_index():

    def upgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.alter_column('users', 'verified',
                   existing_type=sa.BOOLEAN(),
                   nullable=True)
        ### end Alembic commands ###
    
    
    def downgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.alter_column('users', 'verified',
                   existing_type=sa.BOOLEAN(),
                   nullable=False)
        ### end Alembic commands ###
    

    so please make sure you are A. at least on SQLAlchemy 0.8.4 and B. using the git master of Alembic as these fixes aren't released yet.

  17. Mike Bayer repo owner

    although I have identified that we now are generating the UNIQUE constraint twice in the initial autogenerate. need to tweak that.

  18. Alex Kir

    First of all sorry, my bad, i'm on MySQL here, psql is on production.

     ± pip freeze | grep -i sqlalchemy && pip freeze|grep -i alemb                                                                                                          ✹ ✭
    Flask-SQLAlchemy==1.0
    SQLAlchemy==0.8.4
    alembic==0.6.2
    

    autogenerate #1

    def upgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.create_table('users',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(length=40), nullable=True),
        sa.Column('email', sa.String(length=30), nullable=True),
        sa.Column('_password', sa.LargeBinary(length=120), nullable=True),
        sa.Column('_salt', sa.String(length=120), nullable=True),
        sa.Column('admin', sa.Integer(), nullable=True),
        sa.Column('verified', sa.Boolean(), nullable=False),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('email'),
        sa.UniqueConstraint('name')
        )
        ### end Alembic commands ###
    
    def downgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.drop_table('users')
        ### end Alembic commands ###
    

    Model no change:

    def upgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.drop_index('user_id', 'lal_entries')
        op.drop_index('entry_id', 'lal_lol')
        op.drop_index('user_id', 'lal_lol')
        op.drop_index('author_id', 'posts')
        ### end Alembic commands ###
    
    def downgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.create_index('author_id', 'posts', [u'author_id'], unique=False)
        op.create_index('user_id', 'lal_lol', [u'user_id'], unique=False)
        op.create_index('entry_id', 'lal_lol', [u'entry_id'], unique=False)
        op.create_index('user_id', 'lal_entries', [u'user_id'], unique=False)
        ### end Alembic commands ###
    

    Model change:

    def upgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.drop_index('user_id', 'lal_entries')
        op.drop_index('entry_id', 'lal_lol')
        op.drop_index('user_id', 'lal_lol')
        op.drop_index('author_id', 'posts')
        op.alter_column('users', 'verified',
                   existing_type=mysql.TINYINT(display_width=1),
                   nullable=True)
        ### end Alembic commands ###
    
    def downgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.alter_column('users', 'verified',
                   existing_type=mysql.TINYINT(display_width=1),
                   nullable=False)
        op.create_index('author_id', 'posts', [u'author_id'], unique=False)
        op.create_index('user_id', 'lal_lol', [u'user_id'], unique=False)
        op.create_index('entry_id', 'lal_lol', [u'entry_id'], unique=False)
        op.create_index('user_id', 'lal_entries', [u'user_id'], unique=False)
        ### end Alembic commands ###
    

    And again sorry for confusion the database is mysql Ver 15.1 Distrib 5.5.34-MariaDB

  19. Alex Kir

    Upgraded:

       40351ce..85089e1  master     -> origin/master
    

    First autogenerate

    def upgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.create_table('users',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(length=40), nullable=True),
        sa.Column('email', sa.String(length=30), nullable=True),
        sa.Column('_password', sa.LargeBinary(length=120), nullable=True),
        sa.Column('_salt', sa.String(length=120), nullable=True),
        sa.Column('admin', sa.Integer(), nullable=True),
        sa.Column('verified', sa.Boolean(), nullable=False),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('email'),
        sa.UniqueConstraint('name')
        )
        ### end Alembic commands ###
    
    def downgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.drop_table('users')
        ### end Alembic commands ###
    

    No changes:

    def upgrade():
        ### commands auto generated by Alembic - please adjust! ###
        pass
        ### end Alembic commands ###
    
    
    def downgrade():
        ### commands auto generated by Alembic - please adjust! ###
        pass
        ### end Alembic commands ###
    

    Changed nullable=False to True

    def upgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.alter_column('users', 'verified',
                   existing_type=mysql.TINYINT(display_width=1),
                   nullable=True)
        ### end Alembic commands ###
    
    
    def downgrade():
        ### commands auto generated by Alembic - please adjust! ###
        op.alter_column('users', 'verified',
                   existing_type=mysql.TINYINT(display_width=1),
                   nullable=False)
        ### end Alembic commands ###
    

    Worked perfectly i guess. Thanks, you are great <3 :)

  20. Log in to comment