Fix checks for whether a database type is installed

[dmos62]

Problem description

While reviewing #1022, @mathemancer noticed that some of our methods used to determine whether or not a database type is installed on the database are problematic. The following comment by @mathemancer gives hints:

I noticed a problem with the function that checks whether a type is installed. To be honest, that logic is a bit all over the place both in the db/types/base.py file as well as the db/types/operations/cast.py file, and should probably be tidied up.

Mentioned code areas:

• db/types/base.py::get_available_types;
• db/types/operations/cast.py.

Expected outcome

Refactor relevant files and methods to have an accurate picture of which types are installed (on a given database) and which are not.

[dmos62]

@mathemancer To be clear, is the problem that we're using engine.dialect.ischema_names to check whether a type is on the database? Since the "engine mapping", as you've called it, is not proxy for what is installed on the database?

Is this get_available_types method then the root of the problem?

def get_available_types(engine):
    """
    Returns a dict where the keys are database type names defined on the database associated with
    provided Engine, and the values are their SQLAlchemy classes.
    """
    return engine.dialect.ischema_names

Below is what I did to get the installed functions.

def _get_functions_defined_on_database(engine):
    metadata = MetaData()
    pg_proc = Table('pg_proc', metadata, autoload_with=engine, schema='pg_catalog')
    select_statement = select(pg_proc.c.proname)
    return tuple(
        function_name
        for function_name, in engine.connect().execute(select_statement)
    )

Is the solution to do the same for types?

[github-actions]

This issue has not been updated in 90 days and is being marked as stale.