PGFunction does not work < pg 10 (min officially supported version is 11) about alembic_utils HOT 8 CLOSED

Hi Remi,

The update 0.2.8 included a fix to this bug #10 what's causing the problem for you because pg_proc did not have a prokind column prior to pg 11

While the mnimum officially supported version of postgres for alembic_utils is pg 11+, its a small community so if you can come up with an update to this query that with better backward compatibility I'll be happy to take a look and potentially merge.

this is the offending line

from alembic_utils.

If that isn't feasible for you, another option is to patch PGFunction so you can continue using the latest release vs being stuck on 0.2.7

This is not officially supported and may break at any time without a deprecation cycle.

# your_project.pg_function
from alembic_utils.pg_function import PGFunction

class PG10Function(PGFunction):

    @classmethod
    def from_database(cls, connection, schema) -> List[PGFunction]:
        """Get a list of all functions defined in the db"""
        sql = sql_text(
            f"""
        with extension_functions as (
            select
                objid as extension_function_oid
            from
                pg_depend
            where
                -- depends on an extension
                deptype='e'
                -- is a proc/function
                and classid = 'pg_proc'::regclass
        )
        select
            n.nspname as function_schema,
            p.proname as function_name,
            pg_get_function_arguments(p.oid) as function_arguments,
            case
                when l.lanname = 'internal' then p.prosrc
                else pg_get_functiondef(p.oid)
            end as create_statement,
            t.typname as return_type,
            l.lanname as function_language
        from
            pg_proc p
            left join pg_namespace n on p.pronamespace = n.oid
            left join pg_language l on p.prolang = l.oid
            left join pg_type t on t.oid = p.prorettype
            left join extension_functions ef on p.oid = ef.extension_function_oid
        where
            n.nspname not in ('pg_catalog', 'information_schema')
            -- Filter out functions from extensions
            and ef.extension_function_oid is null
            and n.nspname::text = '{schema}';
        """
        )
        rows = connection.execute(sql).fetchall()
        db_functions = [PGFunction.from_sql(x[3]) for x in rows]

        for func in db_functions:
            assert func is not None

        return db_functions

from alembic_utils.

Perhaps you can run this query in first step :

SELECT column_name
FROM information_schema.columns
where
table_schema = 'pg_catalog' and
table_name   = 'pg_proc' and
column_name = 'prokind';

If this query returns a row, that means the column prokind exists in pg_proc, so you can use it in the main query.
Else, do not set the line and p.prokind = 'f' into the main query.
Without the offending line, the query returns expected result.

from alembic_utils.

pg_proc includes functions, procedures, aggregate functions and window functions. prokind=f filters to functions only because PGFunction does not support the other 3 options.

Checking if prokind exists, and omitting it when it does not, does not solve that problem on pg 10.

There is a related field in pg10 named pg_proc.isagg to identify aggregates, but there is no equivalent for procedures and window functions.

from alembic_utils.

Procedures exist only until PG 11:
https://www.postgresql.org/docs/11/sql-createprocedure.html
In PG 10, procedures are only functions that return nothing. So procedure = function.

In PG 10, you can use those 2 columns :

• pg_proc.proisagg : boolean true if aggregates
• pg_proc.proiswindow : boolean true if window

Hope this will help.

from alembic_utils.

thank you for following up. I haven't had time to dig through this yet but it sounds promising

from alembic_utils.

This specific issue is fixed in alembic_utils==0.2.10 but I do want to reiterate that pg 10 is unsupported and may break without a deprecation cycle.

The patch was made in this instance because it was a minimal change, the issue author proposed a reasonable fix, and the community is small enough that I'd like to help out where possible.

Hope it helps!

from alembic_utils.

from alembic_utils.