Misleading error message when updated function has syntax errors

[ddemidov]

Hello, I got hit with this error while on the async_engine branch, so not sure if this is applicable to master.
When migrating one of my functions, I made a syntax error in the new body, which resulted in the following error, which says it can not create the function because it already exists:

  File "/home/demidov/work/rndflow/api/env/lib/python3.9/site-packages/alembic_utils/replaceable_entity.py", line 286, in compare_registered_entities
    maybe_op = entity.get_required_migration_op(
  File "/home/demidov/work/rndflow/api/env/lib/python3.9/site-packages/alembic_utils/replaceable_entity.py", line 158, in get_required_migration_op
    db_def = self.get_database_definition(sess, dependencies=dependencies)
  File "/home/demidov/work/rndflow/api/env/lib/python3.9/site-packages/alembic_utils/replaceable_entity.py", line 97, in get_database_definition
    with simulate_entity(sess, self, dependencies) as sess:
  File "/usr/lib/python3.9/contextlib.py", line 119, in __enter__
    return next(self.gen)
  File "/home/demidov/work/rndflow/api/env/lib/python3.9/site-packages/alembic_utils/simulate.py", line 62, in simulate_entity
    sess.execute(entity.to_sql_statement_create())
  File "/home/demidov/work/rndflow/api/env/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1689, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/home/demidov/work/rndflow/api/env/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1611, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/home/demidov/work/rndflow/api/env/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/home/demidov/work/rndflow/api/env/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1478, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/demidov/work/rndflow/api/env/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1842, in _execute_context
    self._handle_dbapi_exception(
  File "/home/demidov/work/rndflow/api/env/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2023, in _handle_dbapi_exception
    util.raise_(
  File "/home/demidov/work/rndflow/api/env/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/demidov/work/rndflow/api/env/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1799, in _execute_context
    self.dialect.do_execute(
  File "/home/demidov/work/rndflow/api/env/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
  File "/home/demidov/work/rndflow/api/env/lib/python3.9/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 449, in execute
    self._adapt_connection.await_(
  File "/home/demidov/work/rndflow/api/env/lib/python3.9/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 76, in await_only
    return current.driver.switch(awaitable)
  File "/home/demidov/work/rndflow/api/env/lib/python3.9/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 129, in greenlet_spawn
    value = await result
  File "/home/demidov/work/rndflow/api/env/lib/python3.9/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 424, in _prepare_and_execute
    self._handle_exception(error)
  File "/home/demidov/work/rndflow/api/env/lib/python3.9/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 359, in _handle_exception
    self._adapt_connection._handle_exception(error)
  File "/home/demidov/work/rndflow/api/env/lib/python3.9/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 652, in _handle_exception
    raise translated_error from error
sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.DuplicateFunctionError'>: function "job_after_created" already exists with same argument types
[SQL: CREATE FUNCTION "public"."job_after_created"() returns trigger as
$$
begin
    perform pg_notify('rndflow', json_build_object(
        'project', new.project_id,
        'node',    new.node_id,
        'layer',   new.data_layer_id,
        'job',     new.id,
        'event',   'job_created'
        )::text);

    perform pg_notify('rndflow', json_build_object(
        'executor', coalesce(n.executor_id, p.executor_id)
        'event', job_created
        )::text)
    from nodes n, projects p
    where n.id = new.node_id
      and p.id = new.project_id
      and coalesce(n.executor_id, p.executor_id) is not null;

    return null;
end;
$$ language plpgsql]
(Background on this error at: https://sqlalche.me/e/14/f405)

The exception is thrown here in the alembic_utils code:

alembic_utils/src/alembic_utils/simulate.py

Lines 46 to 63 in f6370b0

	try:
	sess.begin_nested()
	sess.execute(entity.to_sql_statement_drop(cascade=True))
	sess.execute(entity.to_sql_statement_create())
	did_yield = True
	yield sess
	except:
	if did_yield:
	# the error came from user code after the yield
	# so we can exit
	raise
	# Try again without the drop in case the drop raised
	# a does not exist error
	sess.rollback()
	sess.begin_nested()
	sess.execute(entity.to_sql_statement_create())
	yield sess

Looks like my syntax error got caught by the try block, but somehow passed the did_yield guard.

[olirice]

That is also present on master

Error messages are definitely a weak point
I'd be happy to review a PR but its enough of an edge case + related to invalid sql syntax so I don't have immediate plans to resolve

will leave it this open in case others see the same error