PGTrigger does not handle correctly table names that need quoting #150
Description
With a PGTrigger definition of
insert_subscriber_info_last_modified = PGTrigger(
schema="public",
signature="insert_true_parent",
definition=f"""
BEFORE INSERT on "Interfaces"
FOR EACH ROW
EXECUTE PROCEDURE proc_update_true_parent();
""",
on_entity=f'"Interfaces"'
)
I get the following error when running alembic with revision --autogenerate
2025-03-10 15:45:05,349 alembic[1686666][INFO][alembic_utils.replaceable_entity]: Detecting required migration op PGTrigger PGTrigger: public.insert_true_parent False public.Interfaces
Traceback (most recent call last):
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
self.dialect.do_execute(
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.UndefinedTable: relation "public.interfaces" does not exist
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/ipdb/__main__.py", line 318, in main
pdb._run(stdlib_pdb._ScriptTarget(mainpyfile))
File "/pipedream/local/venv/deepfield-env/lib/python3.11/pdb.py", line 1643, in _run
self.run(target.code)
File "/pipedream/local/venv/deepfield-env/lib/python3.11/bdb.py", line 600, in run
exec(cmd, globals, locals)
File "<string>", line 1, in <module>
File "/pipedream/local/venv/deepfield-env/bin/alembic", line 10, in <module>
sys.exit(main())
^^^^^^
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/alembic/config.py", line 641, in main
CommandLine(prog=prog).main(argv=argv)
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/alembic/config.py", line 631, in main
self.run_cmd(cfg, options)
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/alembic/config.py", line 608, in run_cmd
fn(
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/alembic/command.py", line 236, in revision
script_directory.run_env()
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/alembic/script/base.py", line 583, in run_env
util.load_python_file(self.dir, "env.py")
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 95, in load_python_file
module = load_module_py(module_id, path)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 113, 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 "/home/support/pipedream/defender_upgrader/alembic/env.py", line 110, in <module>
run_migrations()
File "/home/support/pipedream/defender_upgrader/alembic/env.py", line 107, in run_migrations
context.run_migrations()
File "<string>", line 8, in run_migrations
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/alembic/runtime/environment.py", line 948, in run_migrations
self.get_context().run_migrations(**kw)
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/alembic/runtime/migration.py", line 615, in run_migrations
for step in self._migrations_fn(heads, self):
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/alembic/command.py", line 212, in retrieve_migrations
revision_context.run_autogenerate(rev, context)
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/alembic/autogenerate/api.py", line 570, in run_autogenerate
self._run_environment(rev, migration_context, True)
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/alembic/autogenerate/api.py", line 617, in _run_environment
compare._populate_migration_script(
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/alembic/autogenerate/compare.py", line 65, in _populate_migration_script
_produce_net_changes(autogen_context, upgrade_ops)
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/alembic/autogenerate/compare.py", line 98, in _produce_net_changes
comparators.dispatch("schema", autogen_context.dialect.name)(
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/alembic/util/langhelpers.py", line 313, in go
fn(*arg, **kw)
File "/home/support/.docker_exec_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/support/.docker_exec_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/support/.docker_exec_local/lib/python3.11/site-packages/alembic_utils/replaceable_entity.py", line 104, in get_database_definition
sess.execute(self.to_sql_statement_drop())
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 1717, in execute
result = conn._execute_20(statement, params or {}, execution_options)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1710, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
return connection._execute_clauseelement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1577, in _execute_clauseelement
ret = self._execute_context(
^^^^^^^^^^^^^^^^^^^^^^
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
self._handle_dbapi_exception(
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2134, in _handle_dbapi_exception
util.raise_(
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
self.dialect.do_execute(
File "/pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "public.interfaces" does not exist
[SQL: DROP TRIGGER "insert_true_parent" ON public.Interfaces ]
(Background on this error at: https://sqlalche.me/e/14/f405)
Uncaught exception. Entering post mortem debugging
Running 'cont' or 'step' will restart the program
> /pipedream/local/venv/deepfield-env/lib/python3.11/site-packages/sqlalchemy/engine/default.py(736)do_execute()
735 def do_execute(self, cursor, statement, parameters, context=None):
--> 736 cursor.execute(statement, parameters)
737
ipdb> pp statement
'DROP TRIGGER "insert_true_parent" ON public.Interfaces '
On further inspection to_sql_statement_create is generating the correct SQL because it is parsing the definition member of the PGTrigger whilst to_sql_statement_drop is using the on_entity member of the PGTrigger, which has previously been passed through statement.coerce_to_unquoted
> /home/support/.docker_exec_local/lib/python3.11/site-packages/alembic_utils/replaceable_entity.py(104)get_database_definition()
103 # Drop self
--> 104 sess.execute(self.to_sql_statement_drop())
105
ipdb> pp self.to_sql_statement_create().text
('CREATE TRIGGER "insert_true_parent" BEFORE INSERT ON public."Interfaces" FOR '
'EACH ROW\n'
' EXECUTE PROCEDURE proc_update_true_parent()')
ipdb> pp self.to_sql_statement_drop().text
'DROP TRIGGER "insert_true_parent" ON public.Interfaces '
ipdb>
to_sql_statement_drop needs to do a similar parsing of definition in order to get the correct on_entity to use, like to_sql_statement_create does.