Autogenerate detected removed index, for constraints

[sqlalchemy-bot]

Migrated issue, originally created by John Kida (@jkida)

(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.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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 !

[sqlalchemy-bot]

John Kida (@jkida) wrote:

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.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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.

[sqlalchemy-bot]

John Kida (@jkida) wrote:

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!

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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

[sqlalchemy-bot]

rachidb (@rach) wrote:

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?

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

not sure, any chance you want to try it out for me?

[sqlalchemy-bot]

Zeyi Fan (@fanzeyi) wrote:

Same issue here.

Not PSQL, I use MySQL.

[sqlalchemy-bot]

Zeyi Fan (@fanzeyi) wrote:

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.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

i've reproduced and corrected for all the MySQL issues here in 40351ce, which also reworks the entire system to accommodate quirks across all of SQLite, Postgresql, MySQL.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

if everyone mentioned in this ticket can please test against master today, I can get the release out ASAP.

[sqlalchemy-bot]

AlexK (@Osleg) wrote:

Still happens for me, getting drop_index in migration script even if drop is not intended in models.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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 ###

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

also the above is against MySQL 5.5.29.

[sqlalchemy-bot]

AlexK (@Osleg) wrote:

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

[sqlalchemy-bot]

AlexK (@Osleg) wrote:

The above is against psql (PostgreSQL) 9.3.2

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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

[sqlalchemy-bot]

AlexK (@Osleg) wrote:

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

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

are you sure that's 40351ce of alembic? That I see the two UniqueConstraint in the create_table() and not the separate add_constraint() repeating it, which I'm fixing right now, seems like it's not.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

work is ongoing

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to reopened

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

the UniqueConstraint now renders just inline within the create table if the table is being created, that's 85089e1.

[sqlalchemy-bot]

AlexK (@Osleg) wrote:

40351ce that what i'm using, i will try to upgrade now and will report back

[sqlalchemy-bot]

AlexK (@Osleg) wrote:

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 :)

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

OK I'm pretty confident we've at least improved the situation greatly here

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed