Flask-migrate: upgrade tries to drop native PostGIS tables

[mvexel]

I have a PostGIS enabled PostgreSQL database I want to perform migrations on, but upon python manage.py db upgrade I get (last few lines):

File "/Users/martijnv/.virtualenvs/mr/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 425, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.InternalError: (InternalError) cannot drop table spatial_ref_sys because extension postgis requires it
HINT:  You can drop extension postgis instead.
'\nDROP TABLE spatial_ref_sys' {}

Not being familiar with alembic I don't know if this is something I can make go away by manually editing the migration file, or if it's a bug in alembic, or in flask-migrate.

[miguelgrinberg]

I think your migration script has a drop table for spatial_ref_sys. This could be because this table is not represented by a model, so Alembic thinks you deleted it. You can just remove any references to this table in your migration scripts, and then Alembic will let it be.

[mvexel]

Seems to be it -- this is a table that is auto-generated when you load the PostGIS extension in a db. Thanks.

[renejahn]

Even if this is closed....is there any way to exclude tables, so alembic won't add them to the migration scripts? I have just added postgis for a project and would be glad if I would not have to fix all migratescripts from now on?

[miguelgrinberg]

@renejahn yes, you can use the include_object configuration item in env.py: http://alembic.readthedocs.org/en/latest/api/runtime.html?highlight=include_object#alembic.runtime.environment.EnvironmentContext.configure.params.include_object.

[renejahn]

@miguelgrinberg Thanks! I will have a look at that.

[stefaang]

As the link doesn't work anymore, you may use this
https://gist.github.com/utek/6163250

[miguelgrinberg]

The updated location of the include_object documentation is: http://alembic.zzzcomputing.com/en/latest/api/runtime.html?highlight=include_object#alembic.runtime.environment.EnvironmentContext.configure.params.include_object

[nk9]

Just a note for anyone trying to get this to work: env.py has TWO functions which need to be modified to use your modified include_object: run_migrations_online and run_migrations_offline.

See this gist for an example of this solution implemented.

[abhijithvijayan]

@nk9 Do I have to replace both the functions. Coz it seems like most of the code changed since the last time the gist was updated.

[nk9]

It's not a matter of replacing the contents of the online and offline functions. You just need to add the appropriate code to ignore the table in both places. As @miguelgrinberg said above, you need to define an include_object function which excludes the table(s) in question. Then make sure to pass it as a parameter to context.configure() in both functions.

[abhijithvijayan]

Could you give a snippet for that? I already tried modifying it so many times and now I edit the generated script manually and remove the lines.

[nk9]

Well, it's right there in the gist:

def include_object(object, name, type_, reflected, compare_to):    
    if type_ == "table" and name == 'spatial_ref_sys':
        return False
    else:
        return True

def run_migrations_offline():
    #[…]
    context.configure(# whatever generated params already exist, then
                      include_object=include_object)
    # […]

And the same for the other function.