flask-migrate cannot drop table because other objects depend on it

Question

Problem applying just created migration (Added db.Model) through Flask-Migrate (SQLAlchemy) for PostgresSQL DB. The error itself:

sqlalchemy.exc.InternalError: (psycopg2.InternalError) cannot drop table parameter_subtype because other objects depend on it

Full error stack trace is:

INFO  [alembic.autogenerate.compare] Detected removed foreign key (event_id) (id) on table stage_has_event
INFO  [alembic.autogenerate.compare] Detected removed column 'stage_has_event.event_id'
  Generating /app/migrations/versions/e224df1a4818_.py ... done
(venv) $ ./manage db upgrade
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade cadf22871ae0 -> e224df1a4818, empty message
Traceback (most recent call last):
  File "/app/venv/bin/flask", line 11, in <module>
    sys.exit(main())
  File "/app/venv/lib/python2.7/site-packages/flask_cli/cli.py", line 502, in main
    cli.main(args=args, prog_name=name)
  File "/app/venv/lib/python2.7/site-packages/flask_cli/cli.py", line 369, in main
    return AppGroup.main(self, *args, **kwargs)
  File "/app/venv/lib/python2.7/site-packages/click/core.py", line 697, in main
    rv = self.invoke(ctx)
  File "/app/venv/lib/python2.7/site-packages/click/core.py", line 1066, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/app/venv/lib/python2.7/site-packages/click/core.py", line 1066, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/app/venv/lib/python2.7/site-packages/click/core.py", line 895, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/app/venv/lib/python2.7/site-packages/click/core.py", line 535, in invoke
    return callback(*args, **kwargs)
  File "/app/venv/lib/python2.7/site-packages/click/decorators.py", line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/app/venv/lib/python2.7/site-packages/flask/cli.py", line 257, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "/app/venv/lib/python2.7/site-packages/click/core.py", line 535, in invoke
    return callback(*args, **kwargs)
  File "/app/venv/lib/python2.7/site-packages/flask_migrate/cli.py", line 134, in upgrade
    _upgrade(directory, revision, sql, tag, x_arg)
  File "/app/venv/lib/python2.7/site-packages/flask_migrate/__init__.py", line 259, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
  File "/app/venv/lib/python2.7/site-packages/alembic/command.py", line 254, in upgrade
    script.run_env()
  File "/app/venv/lib/python2.7/site-packages/alembic/script/base.py", line 427, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/app/venv/lib/python2.7/site-packages/alembic/util/pyfiles.py", line 81, in load_python_file
    module = load_module_py(module_id, path)
  File "/app/venv/lib/python2.7/site-packages/alembic/util/compat.py", line 141, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "migrations/env.py", line 87, in <module>
    run_migrations_online()
  File "migrations/env.py", line 80, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/app/venv/lib/python2.7/site-packages/alembic/runtime/environment.py", line 836, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/app/venv/lib/python2.7/site-packages/alembic/runtime/migration.py", line 330, in run_migrations
    step.migration_fn(**kw)
  File "/app/migrations/versions/e224df1a4818_.py", line 21, in upgrade
    op.drop_table('parameter_subtype')
  File "<string>", line 8, in drop_table
  File "<string>", line 3, in drop_table
  File "/app/venv/lib/python2.7/site-packages/alembic/operations/ops.py", line 1187, in drop_table
    operations.invoke(op)
  File "/app/venv/lib/python2.7/site-packages/alembic/operations/base.py", line 319, in invoke
    return fn(self, operation)
  File "/app/venv/lib/python2.7/site-packages/alembic/operations/toimpl.py", line 70, in drop_table
    operation.to_table(operations.migration_context)
  File "/app/venv/lib/python2.7/site-packages/alembic/ddl/impl.py", line 203, in drop_table
    self._exec(schema.DropTable(table))
  File "/app/venv/lib/python2.7/site-packages/alembic/ddl/impl.py", line 118, in _exec
    return conn.execute(construct, *multiparams, **params)
  File "/app/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/app/venv/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/app/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1009, in _execute_ddl
    compiled
  File "/app/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/app/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/app/venv/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/app/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/app`enter code here`/venv/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.InternalError: (psycopg2.InternalError) cannot drop table parameter_subtype because other objects depend on it
DETAIL:  constraint parameter_parameter_subtype_id_fkey on table parameter depends on table parameter_subtype
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
 [SQL: '\nDROP TABLE parameter_subtype']

This leads to table not created in PostgreSQL db and columns either. Unsure what may have caused this problem and how to understand this Error message.

I did manually drop the tables and it helped. However how to be with migration. Any Suggestions?

`HINT: Use DROP ... CASCADE to drop the dependent objects too.` should be a hint. You'll have to implement migration logic that deals with those dependent objects. An easy way (but may not fit your use!) is to CASCADE, which will drop those related objects as well. You want to do this by running migrations to ensure you're able to safely rollback and deal with migration errors. You run some risks by dropping things manually without your migration files being aware of it. — sytech, Mar 22 '18 at 14:01
I did drop them through pgAdmin on my local DB. All seems to be working. However I do not seem to find any references in other models. Seems I have looked through them all. — garmoncheg, Mar 22 '18 at 14:02
Like I said, it's risky to do that (dropping from the pgAdmin) without the migration files knowing about it. In other words, your migration is expecting a certain state that is no longer consistent with the *actual* state of the database. It may help if you post your models and describe the exact steps you've taken in the pgAdmin. — sytech, Mar 22 '18 at 14:03

score 19 · Answer 1 · answered Sep 14 '18 at 15:24

19

Flask-Migrate does not read in the database to see the dependencies between the objects. You can achieve a successful migration by reordering the drop_table in your migration file.

What you can do is modify the migration file like so:

op.drop_table("parameter_subtype")
op.drop_table("parameter")

And then run the upgrade command. Normally ordering the drop in this order should solve the problem.

answered Sep 14 '18 at 15:24

Jean-Michel Provencher

705
5
15

Where does 'op' come from? – Frederik Christoffersen Dec 11 '21 at 18:37
3

@FrederikChristoffersen `from alembic import op` – Lionel Hamayon Jan 16 '22 at 17:57
I would have thought Flask-Migrate would handle this since for example, foreign keys are defined on the models e.g. `db.ForeignKey(Table.id)`. FWIW the generated migration script does comment 'please adjust!'. Anyway SqlAlchemy/Alembic is a tool to help productivity, not to replace dev understanding SQL – xlm May 30 '22 at 06:28

Markus Gilboa · Answer 2 · 2022-02-12T12:45:41.440

-1

I got the same error and what worked for me was to first drop the table in the database using the command:

$ DROP TABLE table_name;

And then in the migration file in the upgrade() function delete or comment out the line where it says:

op.drop_table('table_name')

edited Feb 12 '22 at 12:45

answered Feb 12 '22 at 08:44

Markus Gilboa

59
4

flask-migrate cannot drop table because other objects depend on it

2 Answers2

Linked