Alembic autogenerate broken for 'internal' PG functions

[nick4u]

If in alembic migration you create manually following objects:

CREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS
        'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;
        
CREATE TYPE timerange AS RANGE (
            subtype = time,
            subtype_diff = time_subtype_diff
        )

then next alembic autogenerate will fail with ~error

  File "/usr/local/lib/python3.11/site-packages/alembic_utils/pg_function.py", line 162, in <listcomp>
    db_functions = [cls.from_sql(x[3]) for x in rows]
                    ^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/alembic_utils/pg_function.py", line 55, in from_sql
    raise SQLParseFailure(f'Failed to parse SQL into PGFunction """{sql}"""')
alembic_utils.exceptions.SQLParseFailure: Failed to parse SQL into PGFunction """range_constructor3"""

alembic_utils.pg_function.PGFunction.from_sql function will receive string range_constructor3 which does not parse properly
query defined in alembic_utils.pg_function.PGFunction.from_database for this automatically created/internal function will return p.prosrc

            case
                when l.lanname = 'internal' then p.prosrc
                else pg_get_functiondef(p.oid)
            end as create_statement,

Interestingly for this particular internal function query select pg_get_functiondef(22343); returns

CREATE OR REPLACE FUNCTION public.timerange(time without time zone, time without time zone, text)
 RETURNS timerange
 LANGUAGE internal
 IMMUTABLE PARALLEL SAFE
AS $function$range_constructor3$function$

Which may be enough just to compare code/db state (just guessing)

[olirice]

hi nick

I was not able to reproduce using those steps

I opened PR that adds a test following the steps listed ^ #111

could you checkout that PR, see if it runs for you, and lmk how that goes?

If it does work, please double check that setup steps required to reproduce

[nick4u]

oh my - I am using sqlalchemy 1.4.48

> git show
commit 6fcc8d6b7c8c5dca47736884462e14fbf9d61715 (HEAD -> master, origin/master, origin/HEAD)
Merge: d5d6951 acb1b25
Author: Oliver Rice <[email protected]>
Date:   Mon May 8 12:51:19 2023 -0500

    Merge pull request #108 from GLEF1X/master

    Fix typo: no trailing comma

added your test, and:

> pytest -vv src/test/test_110.py
============================================================================================================================================================= test session starts =============================================================================================================================================================
platform darwin -- Python 3.11.4, pytest-7.3.2, pluggy-1.2.0 -- /Users/nick4u/Documents/git/alembic_utils/.venv/bin/python
cachedir: .pytest_cache
rootdir: /Users/nick4u/Documents/git/alembic_utils
configfile: pytest.ini
collected 1 item

src/test/test_110.py::test_issue_110 FAILED                                                                                                                                                                                                                                                                                             [100%]

================================================================================================================================================================== FAILURES ===================================================================================================================================================================
_______________________________________________________________________________________________________________________________________________________________ test_issue_110 ________________________________________________________________________________________________________________________________________________________________

engine = Engine(postgresql://alem_user:***@localhost:5610/alem_db)

    def test_issue_110(engine) -> None:
        register_entities([TO_UPPER], entity_types=[PGFunction])

        with engine.connect() as con:
            con.execute(
                text(
                    """
            CREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS
                'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;

            CREATE TYPE timerange AS RANGE (
                subtype = time,
                subtype_diff = time_subtype_diff
            )
            """
                )
            )

>       run_alembic_command(
            engine=engine,
            command="revision",
            command_kwargs={"autogenerate": True, "rev_id": "1", "message": "create"},
        )

src/test/test_110.py:36:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
src/alembic_utils/testbase.py:47: in run_alembic_command
    command_func(alembic_cfg, **command_kwargs)
.venv/lib/python3.11/site-packages/alembic/command.py:236: in revision
    script_directory.run_env()
.venv/lib/python3.11/site-packages/alembic/script/base.py:582: in run_env
    util.load_python_file(self.dir, "env.py")
.venv/lib/python3.11/site-packages/alembic/util/pyfiles.py:94: in load_python_file
    module = load_module_py(module_id, path)
.venv/lib/python3.11/site-packages/alembic/util/pyfiles.py:110: in load_module_py
    spec.loader.exec_module(module)  # type: ignore
<frozen importlib._bootstrap_external>:940: in exec_module
    ???
<frozen importlib._bootstrap>:241: in _call_with_frames_removed
    ???
src/test/alembic_config/env.py:75: in <module>
    run_migrations_online()
src/test/alembic_config/env.py:72: in run_migrations_online
    context.run_migrations()
<string>:8: in run_migrations
    ???
.venv/lib/python3.11/site-packages/alembic/runtime/environment.py:928: in run_migrations
    self.get_context().run_migrations(**kw)
.venv/lib/python3.11/site-packages/alembic/runtime/migration.py:615: in run_migrations
    for step in self._migrations_fn(heads, self):
.venv/lib/python3.11/site-packages/alembic/command.py:212: in retrieve_migrations
    revision_context.run_autogenerate(rev, context)
.venv/lib/python3.11/site-packages/alembic/autogenerate/api.py:562: in run_autogenerate
    self._run_environment(rev, migration_context, True)
.venv/lib/python3.11/site-packages/alembic/autogenerate/api.py:609: in _run_environment
    compare._populate_migration_script(
.venv/lib/python3.11/site-packages/alembic/autogenerate/compare.py:59: in _populate_migration_script
    _produce_net_changes(autogen_context, upgrade_ops)
.venv/lib/python3.11/site-packages/alembic/autogenerate/compare.py:93: in _produce_net_changes
    comparators.dispatch("schema", autogen_context.dialect.name)(
.venv/lib/python3.11/site-packages/alembic/util/langhelpers.py:269: in go
    fn(*arg, **kw)
src/alembic_utils/replaceable_entity.py:328: in compare_registered_entities
    maybe_op = entity.get_required_migration_op(sess, dependencies=has_create_or_update_op)
src/alembic_utils/replaceable_entity.py:161: in get_required_migration_op
    entities_in_database: List[T] = self.from_database(sess, schema=self.schema)
src/alembic_utils/pg_function.py:164: in from_database
    db_functions = [cls.from_sql(x[3]) for x in rows]
src/alembic_utils/pg_function.py:164: in <listcomp>
    db_functions = [cls.from_sql(x[3]) for x in rows]
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

cls = <class 'alembic_utils.pg_function.PGFunction'>, sql = 'range_constructor2'

    @classmethod
    def from_sql(cls, sql: str) -> "PGFunction":
        """Create an instance instance from a SQL string"""
        template = "create{}function{:s}{schema}.{signature}{:s}returns{:s}{definition}"
        result = parse(template, sql.strip(), case_sensitive=False)
        if result is not None:
            # remove possible quotes from signature
            raw_signature = result["signature"]
            signature = (
                "".join(raw_signature.split('"', 2))
                if raw_signature.startswith('"')
                else raw_signature
            )
            return cls(
                schema=result["schema"],
                signature=signature,
                definition="returns " + result["definition"],
            )
>       raise SQLParseFailure(f'Failed to parse SQL into PGFunction """{sql}"""')
E       alembic_utils.exceptions.SQLParseFailure: Failed to parse SQL into PGFunction """range_constructor2"""

src/alembic_utils/pg_function.py:57: SQLParseFailure
------------------------------------------------------------------------------------------------------------------------------------------------------------ Captured stderr call -------------------------------------------------------------------------------------------------------------------------------------------------------------
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
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 PGFunction PGFunction: public.toUpper(some_text text default 'my text!')
============================================================================================================================================================== warnings summary ===============================================================================================================================================================
src/test/test_110.py::test_issue_110
  /Users/nick4u/Documents/git/alembic_utils/src/test/test_110.py:22: RemovedIn20Warning: Deprecated API features detected! These feature(s) are not compatible with SQLAlchemy 2.0. To prevent incompatible upgrades prior to updating applications, ensure requirements files are pinned to "sqlalchemy<2.0". Set environment variable SQLALCHEMY_WARN_20=1 to show all deprecation warnings.  Set environment variable SQLALCHEMY_SILENCE_UBER_WARNING=1 to silence this message. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
    con.execute(

-- Docs: https://docs.pytest.org/en/stable/how-to/capture-warnings.html
=========================================================================================================================================================== short test summary info ===========================================================================================================================================================
FAILED src/test/test_110.py::test_issue_110 - alembic_utils.exceptions.SQLParseFailure: Failed to parse SQL into PGFunction """range_constructor2"""
======================================================================================================================================================== 1 failed, 1 warning in 0.27s =========================================================================================================================================================

for "sqlalchemy>=1.4,<2"

> pip list
Package           Version Editable project location
----------------- ------- --------------------------------------------
alembic           1.11.1
alembic-utils     0.8.1   /Users/nick4u/Documents/git/alembic_utils
alembic-utils     0.8.1
coverage          7.2.7
flupy             1.2.0
iniconfig         2.0.0
Mako              1.2.4
MarkupSafe        2.1.3
mkdocs            1.4.3
packaging         23.1
parse             1.19.1
pip               22.3.1
pluggy            1.2.0
psycopg2-binary   2.9.6
pytest            7.3.2
pytest-cov        4.1.0
setuptools        65.5.1
SQLAlchemy        1.4.48
typing_extensions 4.6.3
wheel             0.38.4

after pip install sqlalchemy --upgrade all tests pass

[olirice]

after pip install sqlalchemy --upgrade all tests pass

thanks, got the same result on 1.4
The same SQL statement is returning different results under 1.4 and 2.0 and I have no idea why that would be

---

the special case for internal here avoids a SQL exception when calling pg_get_functiondef on internal functions

      case
          when l.lanname = 'internal' then p.prosrc
          else pg_get_functiondef(p.oid)
      end as create_statement

but internal functions will always fail to parse. You're encountering this issue because internal functions are typically limited to information_schema and pg_catalog which are explicitly filtered out.

---

Since your function is on the search path of alembic_utils I think failing is the appropriate thing for it to do.

I know that isn't a satisfying answer, but I'm not clear what the difference being introduced by SQLA 1.4 -> 2.0 is and the alternatives would be to silently ignore internal functions that are on the search_path, or plumb through a new parameter to let users define custom filtering rules (which would be confusing & break assumptions in include_object).

---

You can avoid the problem doing one of:

• upgrading SQLA
• moving the problematic function into a separate schema and adding it to exclude_schemas in register_entities
• exclude PGFunction from entity_types in register_entities

[nick4u]

You can avoid the problem doing one of:

upgrading SQLA

no such option for me unfortunately

moving the problematic function into a separate schema and adding it to exclude_schemas in register_entities

impossible as this "internal function is created automatically by PG after registering new CREATE TYPE timerange AS RANGE - those 2 functions range_constructor2 and range_constructor3 are automatically created "constructors" for new range

exclude PGFunction from entity_types in register_entities

as mentioned above - there is no corresponding PGFunction in the code also
it is even impossible to block it using alembic's include_object callback in env.py as all detected functions are parsed in single pass

meanwhile, maybe you could configure CI/project to run tests against SA2.0 and 1.4 as it is still used by the majority of users

In my spare time I'll try to find this SQLalchemy 1.4 - 2.0 diff :)

[olirice]

In my spare time I'll try to find this SQLalchemy 1.4 - 2.0 diff :)

thanks, that would be great. I'd love to know the cause

there is no corresponding PGFunction

Are you using any PGFunctions? If you're not, then you can pass entity_types to register_entities and omit PGFunction so they get skipped

impossible as this "internal function is created automatically by PG after registering new CREATE TYPE timerange AS RANGE - those 2 functions range_constructor2 and range_constructor3 are automatically created "constructors" for new range

moving the type also moves the generated functions