gino does not work correctly with enum types in postgresql

[zerlok]

Describe the bug

I'm trying to use gino in postgres with simple model with enum column type. It seems like enum is not fully supported and it behaves really strange in several cases:

1) when using gino Enum type in where filters, asyncpg.exceptions.InternalServerError: cache lookup failed for type 16453 error occurs 2) when using postgresql Enum and running db.gino.create_all(), asyncpg.exceptions.UndefinedObjectError: type "test-schema.enum1" does not exist error occurs

To Reproduce

The full example is in my repo: https://github.com/zerlok/python-bugs/tree/gino/enum-usage

import asyncio
import enum
import os
import typing as t

import gino
import pytest
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import ENUM as PostgresEnum

DB_SCHEMA = os.getenv("SCHEMA")
DB = gino.Gino(schema=DB_SCHEMA)

class Enum1(enum.Enum):
    VAL1 = enum.auto()
    VAL2 = enum.auto()

class Enum2(enum.Enum):
    VAL3 = enum.auto()
    VAL4 = enum.auto()
    VAL5 = enum.auto()

@pytest.mark.asyncio
class TestGinoEnum:

    @pytest.fixture(scope="session")
    async def engine(self):
        async with DB.with_bind(os.getenv("DATABASE")) as engine:
            yield engine

    @pytest.fixture(scope="session")
    def db_model(self, gino_enum_type, engine) -> gino.Gino.Model:
        if gino_enum_type == "postgres":
            class Foo(DB.Model):
                __tablename__ = "foo"

                id = DB.Column(DB.Integer(), primary_key=True)

                # causes asyncpg.exceptions.UndefinedObjectError: type "test.enum1" does not exist
                enum_field1 = DB.Column(PostgresEnum(Enum1, inherit_schema=True), default=Enum1.VAL1, nullable=False)
                enum_field2 = DB.Column(PostgresEnum(Enum2, inherit_schema=True), default=Enum2.VAL3, nullable=False)

        elif gino_enum_type is None:
            class Foo(DB.Model):
                __tablename__ = "foo"

                id = DB.Column(DB.Integer(), primary_key=True)

                # no exception
                enum_field1 = DB.Column(DB.Enum(Enum1, inherit_schema=True), default=Enum1.VAL1, nullable=False)
                enum_field2 = DB.Column(DB.Enum(Enum2, inherit_schema=True), default=Enum2.VAL3, nullable=False)

        else:
            raise ValueError("unknown gino type", gino_enum_type)

        return Foo

    @pytest.fixture()
    async def clean_database(self, gino_enum_type, gino_create_enum_type_manually, engine, db_model):
        await DB.gino.drop_all()

        if DB_SCHEMA:
            await engine.status(f"""drop schema if exists \"{DB_SCHEMA}\" cascade""")
            await engine.status(f"""create schema if not exists \"{DB_SCHEMA}\"""")

        if gino_create_enum_type_manually:
            def quote_value(value: t.Union[Enum1, Enum2]) -> str:
                return f"'{value.name}'"

            async def create_enum(e: t.Union[t.Type[Enum1], t.Type[Enum2]]) -> None:
                if DB_SCHEMA:
                    enum_type = f"\"{DB_SCHEMA}\".{e.__name__.lower()}"
                else:
                    enum_type = f"{e.__name__.lower()}"

                await engine.status(f"""
                    create type {enum_type} as enum ({",".join(quote_value(v) for v in e)})
                """)

            await create_enum(Enum1)
            await create_enum(Enum2)

        else:
            # hope that enum types will be added automatically with `create_all` .
            pass

        await DB.gino.create_all()

    async def test_enum_types_removal(self, engine, clean_database) -> None:
        await DB.gino.drop_all()
        assert (await self.__get_enum_info_from_database(engine)) == []

    async def test_enum_type_creation(self, engine, clean_database) -> None:
        assert (await self.__get_enum_info_from_database(engine)) == sorted(
            (DB_SCHEMA or "public", enum_type.__name__.lower(), val.name)
            for enum_type in (Enum1, Enum2)
            for val in enum_type
        )

    @pytest.mark.parametrize("n", (0, 1, 2))
    async def test_enum_type_filter(self, engine, clean_database, db_model, n) -> None:
        # when n == 2, `the asyncpg.exceptions.InternalServerError: cache lookup failed for type 16453` occurs
        assert (await asyncio.gather(*(
            self.__get_bars(engine, db_model)
            for i in range(n)
        ))) == [[] for i in range(n)]

    async def __get_bars(self, engine: gino.GinoEngine, db_model: gino.Gino.Model) -> [object]:
        val1 = await engine.all(db_model.query.where(db_model.enum_field1 == Enum1.VAL1))
        val4 = await engine.all(
            sa.select([db_model.id]).select_from(db_model).where(db_model.enum_field2 == Enum2.VAL4))

        return [*val1, *val4]

    async def __get_enum_info_from_database(self, engine: gino.GinoEngine) -> t.Sequence[t.Tuple[str, str, str]]:
        return await engine.all("""
            select n.nspname as enum_schema, t.typname as enum_name, e.enumlabel as enum_value
            from pg_type t
            join pg_enum e on t.oid = e.enumtypid
            join pg_catalog.pg_namespace n ON n.oid = t.typnamespace
            order by enum_schema, enum_name, enum_value
        """)

Expected result

I want all my test runs to finish without described exceptions. See README: https://github.com/zerlok/python-bugs/tree/gino/enum-usage#troubles-with-gino-sqlalchemy-postgres-enum

Actual result

For 1 case

FAILED tests/test_gino.py::TestGinoEnum::test_enum_type_filter[2] - asyncpg.exceptions.InternalServerError: cache lookup failed for type 16453

For 2 case

ERROR tests/test_gino.py::TestGinoEnum::test_enum_types_removal - asyncpg.exceptions.UndefinedObjectError: type "test-schema.enum1" does not exist
ERROR tests/test_gino.py::TestGinoEnum::test_enum_type_creation - asyncpg.exceptions.UndefinedObjectError: type "test-schema.enum1" does not exist
ERROR tests/test_gino.py::TestGinoEnum::test_enum_type_filter[0] - asyncpg.exceptions.UndefinedObjectError: type "test-schema.enum1" does not exist
ERROR tests/test_gino.py::TestGinoEnum::test_enum_type_filter[1] - asyncpg.exceptions.UndefinedObjectError: type "test-schema.enum1" does not exist
ERROR tests/test_gino.py::TestGinoEnum::test_enum_type_filter[2] - asyncpg.exceptions.UndefinedObjectError: type "test-schema.enum1" does not exist

Environment (please complete the following information):

asyncpg==0.25.0; python_version >= "3.5" and python_version < "4.0" and python_full_version >= "3.6.0"
atomicwrites==1.4.0; python_version >= "3.7" and python_full_version < "3.0.0" and sys_platform == "win32" or sys_platform == "win32" and python_version >= "3.7" and python_full_version >= "3.4.0"
attrs==21.4.0; python_version >= "3.7" and python_full_version < "3.0.0" or python_full_version >= "3.5.0" and python_version >= "3.7"
click==8.1.3; python_version >= "3.7"
colorama==0.4.4; python_version >= "3.7" and python_full_version < "3.0.0" and sys_platform == "win32" and platform_system == "Windows" or sys_platform == "win32" and python_version >= "3.7" and python_full_version >= "3.5.0" and platform_system == "Windows"
gino==1.0.1; python_version >= "3.5" and python_version < "4.0"
iniconfig==1.1.1; python_version >= "3.7"
packaging==21.3; python_version >= "3.7"
pluggy==1.0.0; python_version >= "3.7"
py==1.11.0; python_version >= "3.7" and python_full_version < "3.0.0" or python_full_version >= "3.5.0" and python_version >= "3.7"
pyparsing==3.0.9; python_full_version >= "3.6.8" and python_version >= "3.7"
pytest-asyncio==0.18.3; python_version >= "3.7"
pytest==7.1.2; python_version >= "3.7"
sqlalchemy==1.3.24; python_version >= "3.5" and python_full_version < "3.0.0" and python_version < "4.0" or python_version >= "3.5" and python_version < "4.0" and python_full_version >= "3.4.0"
tomli==2.0.1; python_version >= "3.7"
wait-for-it==2.2.1; python_version >= "3.7"

postgres: 11.10

Additional context

I described a full info in my repo (it was easier for my) and I provided the full environment (docker image and docker-compose) to reproduce this bug, look at my repo: https://github.com/zerlok/python-bugs/tree/gino/enum-usage.

[zerlok]

I found a "cache lookup failed" related issue in sqlalchemy https://github.com/sqlalchemy/sqlalchemy/issues/6645 . Maybe the 1 described behavior is not a GINO's bug. But I'm not sure about the 2 behavior.

[zerlok]

One more thing. I found that async sqlalchemy (with asyncpg driver) works as expected for me in 1 and 2 cases (full code in repo).

Dependencies:

asyncpg==0.25.0; python_full_version >= "3.6.0"
atomicwrites==1.4.0; python_version >= "3.7" and python_full_version < "3.0.0" and sys_platform == "win32" or sys_platform == "win32" and python_version >= "3.7" and python_full_version >= "3.4.0"
attrs==21.4.0; python_version >= "3.7" and python_full_version < "3.0.0" or python_full_version >= "3.5.0" and python_version >= "3.7"
click==8.1.3; python_version >= "3.7"
colorama==0.4.4; python_version >= "3.7" and python_full_version < "3.0.0" and sys_platform == "win32" and platform_system == "Windows" or sys_platform == "win32" and python_version >= "3.7" and python_full_version >= "3.5.0" and platform_system == "Windows"
greenlet==1.1.2; python_version >= "3" and python_full_version < "3.0.0" and (platform_machine == "aarch64" or platform_machine == "ppc64le" or platform_machine == "x86_64" or platform_machine == "amd64" or platform_machine == "AMD64" or platform_machine == "win32" or platform_machine == "WIN32") and (python_version >= "2.7" and python_full_version < "3.0.0" or python_full_version >= "3.6.0") or python_version >= "3" and (platform_machine == "aarch64" or platform_machine == "ppc64le" or platform_machine == "x86_64" or platform_machine == "amd64" or platform_machine == "AMD64" or platform_machine == "win32" or platform_machine == "WIN32") and (python_version >= "2.7" and python_full_version < "3.0.0" or python_full_version >= "3.6.0") and python_full_version >= "3.5.0"
iniconfig==1.1.1; python_version >= "3.7"
packaging==21.3; python_version >= "3.7"
pluggy==1.0.0; python_version >= "3.7"
py==1.11.0; python_version >= "3.7" and python_full_version < "3.0.0" or python_full_version >= "3.5.0" and python_version >= "3.7"
pyparsing==3.0.9; python_full_version >= "3.6.8" and python_version >= "3.7"
pytest-asyncio==0.18.3; python_version >= "3.7"
pytest==7.1.2; python_version >= "3.7"
sqlalchemy==1.4.36; (python_version >= "2.7" and python_full_version < "3.0.0") or (python_full_version >= "3.6.0")
tomli==2.0.1; python_version >= "3.7"
wait-for-it==2.2.1; python_version >= "3.7"

I see a newer version of sqlalchemy here: sqlalchemy==1.4.36 , so I tried to install this version, but the higher version of sqlalchemy is forbidden by gino constraint

$ poetry add "sqlalchemy^1.4.36"

Updating dependencies
Resolving dependencies... (0.3s)

  SolverProblemError

  Because gino (1.0.1) depends on SQLAlchemy (>=1.2.16,<1.4)
   and no versions of gino match >1.0.1,<2.0.0, gino (>=1.0.1,<2.0.0) requires SQLAlchemy (>=1.2.16,<1.4).
  So, because python-bug-reports depends on both gino (^1.0.1) and SQLAlchemy (^1.4.36), version solving failed.

I'm not 100% sure if sqlalchemy upgrade will solve the problem with enums, but I'd like to ask. What's the reason to deny a higher version of sqlalchemy?

P.S. I found the question on SO about 1 case (described in the first message). It looks like 1.4 sqlalchemy version upgrade won't totaly fix the problem. https://stackoverflow.com/questions/68000969/sqlalchemy-1-4-throws-internalservererror-cache-lookup-failed-for-type-3912040

UPD: found gino develop plans for future and integration with sqlalchemy here.