I'm trying to create a table and a view based on this table in a SQLAlchemy/alembic/PostgreSQL environment, with alembic revision --autogenerate. I could properly setup things so that alembic identifies the table and the view to be created, following the structure proposed here. However, since I'm trying to create a table and the view based on this table on the same migration, problems arise. When it is time to create the view it fails saying that the table does not yet exist. It works fine if I comment out the register_entities([my_view]) from my migrations/env.py file, run the migration once (so it creates the table), then undo the comment and run the migration again (which creates the view). However, I would like to solve this issue so there is no need for any shady process for deploy or for new people working in the repo. I'm not sure if it's trying to create the view before the table or what. Any tips on how to solve this? Here are the full logs from running alembic revision --autogenerate: INFO [alembic.runtime.migration] Context impl PostgresqlImpl. INFO [alembic.runtime.migration] Will assume transactional DDL. INFO [alembic.autogenerate.compare] Detected added table 'my_table' INFO [alembic_utils.depends] Resolving entities with no dependencies INFO [alembic_utils.depends] Resolving entities with dependencies. This may take a minute INFO [alembic_utils.replaceable_entity] Detecting required migration op PGView PGView: public.my_view Traceback (most recent call last): File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context self.dialect.do_execute( File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute cursor.execute(statement, parameters) psycopg2.errors.UndefinedTable: view "my_view" does not exist The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/home/vscode/.local/lib/python3.11/site-packages/alembic_utils/simulate.py", line 47, in simulate_entity sess.execute(entity.to_sql_statement_drop(cascade=True)) File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2306, in execute return self._execute_internal( ^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2197, in _execute_internal result = conn.execute( ^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1416, in execute return meth( ^^^^^ File "/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 516, in _execute_on_connection return connection._execute_clauseelement( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement ret = self._execute_context( ^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context return self._exec_single_context( ^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context self._handle_dbapi_exception( File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2343, in _handle_dbapi_exception raise sqlalchemy_exception.with_traceback(exc_info[2]) from e File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context self.dialect.do_execute( File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) view "my_view" does not exist [SQL: DROP VIEW "public"."my_view" cascade] (Background on this error at: https://sqlalche.me/e/20/f405) During handling of the above exception, another exception occurred: Traceback (most recent call last): File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context self.dialect.do_execute( File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute cursor.execute(statement, parameters) psycopg2.errors.UndefinedTable: relation "my_table" does not exist LINE 2: FROM my_table; ^ The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/usr/local/bin/alembic", line 8, in <module> sys.exit(main()) ^^^^^^ File "/usr/local/lib/python3.11/site-packages/alembic/config.py", line 630, in main CommandLine(prog=prog).main(argv=argv) File "/usr/local/lib/python3.11/site-packages/alembic/config.py", line 624, in main self.run_cmd(cfg, options) File "/usr/local/lib/python3.11/site-packages/alembic/config.py", line 601, in run_cmd fn( File "/usr/local/lib/python3.11/site-packages/alembic/command.py", line 234, in revision script_directory.run_env() File "/usr/local/lib/python3.11/site-packages/alembic/script/base.py", line 579, in run_env util.load_python_file(self.dir, "env.py") File "/usr/local/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 93, in load_python_file module = load_module_py(module_id, path) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 109, in load_module_py spec.loader.exec_module(module) # type: ignore ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "<frozen importlib._bootstrap_external>", line 940, in exec_module File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed File "/workspaces/api/app/migrations/env.py", line 99, in <module> run_migrations_online() File "/workspaces/api/app/migrations/env.py", line 93, in run_migrations_online context.run_migrations() File "<string>", line 8, in run_migrations File "/usr/local/lib/python3.11/site-packages/alembic/runtime/environment.py", line 938, in run_migrations self.get_context().run_migrations(**kw) File "/usr/local/lib/python3.11/site-packages/alembic/runtime/migration.py", line 612, in run_migrations for step in self._migrations_fn(heads, self): ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/alembic/command.py", line 210, in retrieve_migrations revision_context.run_autogenerate(rev, context) File "/usr/local/lib/python3.11/site-packages/alembic/autogenerate/api.py", line 567, in run_autogenerate self._run_environment(rev, migration_context, True) File "/usr/local/lib/python3.11/site-packages/alembic/autogenerate/api.py", line 614, in _run_environment compare._populate_migration_script( File "/usr/local/lib/python3.11/site-packages/alembic/autogenerate/compare.py", line 59, in _populate_migration_script _produce_net_changes(autogen_context, upgrade_ops) File "/usr/local/lib/python3.11/site-packages/alembic/autogenerate/compare.py", line 92, in _produce_net_changes comparators.dispatch("schema", autogen_context.dialect.name)( File "/usr/local/lib/python3.11/site-packages/alembic/util/langhelpers.py", line 268, in go fn(*arg, **kw) File "/home/vscode/.local/lib/python3.11/site-packages/alembic_utils/replaceable_entity.py", line 328, in compare_registered_entities maybe_op = entity.get_required_migration_op(sess, dependencies=has_create_or_update_op) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/vscode/.local/lib/python3.11/site-packages/alembic_utils/replaceable_entity.py", line 163, in get_required_migration_op db_def = self.get_database_definition(sess, dependencies=dependencies) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/vscode/.local/lib/python3.11/site-packages/alembic_utils/replaceable_entity.py", line 102, in get_database_definition with simulate_entity(sess, self, dependencies) as sess: File "/usr/local/lib/python3.11/contextlib.py", line 137, in __enter__ return next(self.gen) ^^^^^^^^^^^^^^ File "/home/vscode/.local/lib/python3.11/site-packages/alembic_utils/simulate.py", line 62, in simulate_entity sess.execute(entity.to_sql_statement_create()) File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2306, in execute return self._execute_internal( ^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2197, in _execute_internal result = conn.execute( ^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1416, in execute return meth( ^^^^^ File "/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 516, in _execute_on_connection return connection._execute_clauseelement( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement ret = self._execute_context( ^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context return self._exec_single_context( ^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context self._handle_dbapi_exception( File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2343, in _handle_dbapi_exception raise sqlalchemy_exception.with_traceback(exc_info[2]) from e File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context self.dialect.do_execute( File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "my_table" does not exist LINE 2: FROM my_table; ^ [SQL: CREATE VIEW "public"."my_view" AS SELECT my_table.id AS id, my_table.field1 AS field1, my_table.field2 AS field2 FROM my_table;] Versions: psycopg2~=2.9 sqlalchemy~=2.0 sqlalchemy-utils~=0.41.1 alembic~=1.10 alembic_utils~=0.8.2 Continue reading...
