Alembic Foreign Keys getting removed and re-added on every new migration

Issue #401 closed
Waseem Jan
created an issue

Every time I create a new revision, Alembic tries to remove the already existing foreign keys and then tries to add them again. Logs looks like this

INFO  [alembic.autogenerate.compare] Detected removed foreign key (post_id)(post_id) on table album_photos
INFO  [alembic.autogenerate.compare] Detected removed foreign key (album_id)(album_id) on table album_photos
INFO  [alembic.autogenerate.compare] Detected removed foreign key (user_id)(user_id) on table album_photos
INFO  [alembic.autogenerate.compare] Detected added foreign key (album_id)(album_id) on table prodcat.album_photos
INFO  [alembic.autogenerate.compare] Detected added foreign key (post_id)(post_id) on table prodcat.album_photos
INFO  [alembic.autogenerate.compare] Detected added foreign key (user_id)(user_id) on table prodcat.album_photos

I have tried adding name to each Foreign Key constraint but that doesn't have any effect. My tables reside in multiple Postgresql Schemas and I have hardcoded the schema information in each model class like this

class AlbumPhotos(db.Model, TimeStampMixin, HasLikes, HasComments):
    __tablename__ = 'album_photos'
    __table_args__ = {'schema' : 'prodcat'}
    id = db.Column('photo_id',db.Integer, primary_key=True)

Here is more detail explanation of the problem http://stackoverflow.com/questions/41150956/alembic-not-generating-correct-changes

Comments (5)

  1. Michael Bayer repo owner

    this is not a bug report, it's a usage question. Your issue is either that your ForeignKeyConstraint definitions (not shown here, so I have no idea) are not specifying the schema correctly, and/or you are not using the correct search path (e.g. "public" and nothing else). one of those two areas is likely where something needs adjustment.

  2. Waseem Jan reporter

    here is how I normally define my models.

    class AlbumPhotos(db.Model, TimeStampMixin, HasLikes, HasComments):
        __tablename__ = 'album_photos'
        __table_args__ = {'schema' : 'schema_b'}
        id = db.Column('photo_id',db.Integer, primary_key=True)
        album_id = db.Column('album_id',db.Integer, db.ForeignKey('prodcat.collage_albums.album_id'),nullable=False)
        photo_name = db.Column('photo_name',db.String, nullable=False)
    
        event_dish_id = db.Column('event_dish_id',db.Integer, db.ForeignKey('prodcat.event_dishes.event_dish_id'))
        post_id = db.Column('post_id',db.Integer, db.ForeignKey('prodcat.posts.post_id'))
    
        user_id = db.Column('user_id', db.Integer,db.ForeignKey('regis.users.user_id'),nullable=False)
    
        event_dish = db.relationship('EventDish', uselist=False, \
                        backref=db.backref('photos', lazy='dynamic', cascade="all,delete-orphan"))
    
        post = db.relationship('Post', uselist=False, \
                        backref=db.backref('photos', lazy='dynamic', cascade="all,delete-orphan"))
        #uploader
        user = db.relationship('User', uselist=False,\
                        backref=db.backref('album_photos', lazy='dynamic',cascade="all,delete-orphan"))
    
        album = db.relationship('CollageAlbum', uselist=False, \
                            backref=db.backref('photos', lazy='dynamic',\
                            cascade="all,delete-orphan"))
    

    This is how I set my search path in DB.

    ALTER ROLE db_user SET search_path = schema_a, schema_b;
    

    I am curious though, if its a search_path issue why is alembic not trying to recreate all tables and instead just trying to recreate Foreign Keys only.

  3. Waseem Jan reporter

    Setting search_path to public fixed this issue. I always thought that in addition to setting schema info explicitly on each model, we also need to add those schemas on search_path. Thanks

  4. Michael Bayer repo owner

    In theory, any approach can be made to work but unfortunately they are not cross-compatible, and SQLAlchemy had to pick one way of working by default, which is that it uses what's in search_path to determine those schema names that it will never requier explicitly in the model. if you want it to work another way you can set postgresql_ignore_search_path.

  5. Log in to comment