Postgres version 12 setup issues

[codemation]

Problem

From easyauth issue, when versoin 12 of postgres is used. aiopyql throws the following exceptions, preventing DB setup.

07-05 00:35 EasyRpc-server /ws/postgres_db ERROR    error running query: 
        create or replace function show_tables() returns SETOF text as $$
        SELECT
            table_name
        FROM
            information_schema.tables
        WHERE
            table_type = 'BASE TABLE'
        AND
            table_schema NOT IN ('pg_catalog', 'information_schema');
        $$
        language sql; 
    
Traceback (most recent call last):
  File "/home/tso/Documents/python/easyauthnz/easyauth/easyauth-env/lib/python3.8/site-packages/aiopyql/data.py", line 393, in __process_queue
    results = await self.process_query_no_commit(self, conn, query_id, query)
  File "/home/tso/Documents/python/easyauthnz/easyauth/easyauth-env/lib/python3.8/site-packages/aiopyql/postgres_connector.py", line 260, in process_query_no_commit
    results = await conn.fetch(query)
  File "/home/tso/Documents/python/easyauthnz/easyauth/easyauth-env/lib/python3.8/site-packages/asyncpg/connection.py", line 583, in fetch
    return await self._execute(
  File "/home/tso/Documents/python/easyauthnz/easyauth/easyauth-env/lib/python3.8/site-packages/asyncpg/connection.py", line 1625, in _execute
    result, _ = await self.__execute(
  File "/home/tso/Documents/python/easyauthnz/easyauth/easyauth-env/lib/python3.8/site-packages/asyncpg/connection.py", line 1650, in __execute
    return await self._do_execute(
  File "/home/tso/Documents/python/easyauthnz/easyauth/easyauth-env/lib/python3.8/site-packages/asyncpg/connection.py", line 1697, in _do_execute
    result = await executor(stmt, None)
  File "asyncpg/protocol/protocol.pyx", line 201, in bind_execute
asyncpg.exceptions.InvalidFunctionDefinitionError: return type mismatch in function declared to return text
DETAIL:  Actual return type is information_schema.sql_identifier.

Investigation - Steps to reproduce

Issue is reproducible in version 12 of postgres

$ mkdir pg_data
$ docker run --name pgtest -e POSTGRES_PASSWORD=abcd1234     -e POSTGRES_DB=postgres_db     -p 5432:5432     -v $(pwd)/pg_data:/var/lib/postgresql/data -d postgres:12

from aiopyql import data

async def main():

    db = await data.Database.create(
        database='postgres_db',
        user='postgres',
        password='abcd1234',
        host='localhost',
        port=5432,
        db_type='postgres',
        cache_enabled=True,
        #debug=True
    )
asyncio.run(main())

python test.py
$ python test.py 
07-05 00:40 aiopyql-db-postgres_db ERROR    error running query: 
        create or replace function show_tables() returns SETOF text as $$
        SELECT
            table_name
        FROM
            information_schema.tables
        WHERE
            table_type = 'BASE TABLE'
        AND
            table_schema NOT IN ('pg_catalog', 'information_schema');
        $$
        language sql; 
    
Traceback (most recent call last):
  File "/home/tso/Documents/python/aiopyql/aiopyql/data.py", line 393, in __process_queue
    results = await self.process_query_no_commit(self, conn, query_id, query)
  File "/home/tso/Documents/python/aiopyql/aiopyql/postgres_connector.py", line 260, in process_query_no_commit
    results = await conn.fetch(query)
  File "/home/tso/Documents/python/aiopyql/aiopyql-env/lib/python3.8/site-packages/asyncpg/connection.py", line 583, in fetch
    return await self._execute(
  File "/home/tso/Documents/python/aiopyql/aiopyql-env/lib/python3.8/site-packages/asyncpg/connection.py", line 1625, in _execute
    result, _ = await self.__execute(
  File "/home/tso/Documents/python/aiopyql/aiopyql-env/lib/python3.8/site-packages/asyncpg/connection.py", line 1650, in __execute
    return await self._do_execute(
  File "/home/tso/Documents/python/aiopyql/aiopyql-env/lib/python3.8/site-packages/asyncpg/connection.py", line 1697, in _do_execute
    result = await executor(stmt, None)
  File "asyncpg/protocol/protocol.pyx", line 201, in bind_execute
asyncpg.exceptions.InvalidFunctionDefinitionError: return type mismatch in function declared to return text
DETAIL:  Actual return type is information_schema.sql_identifier.

Workaround

Use postgres version 11 or 13

[codemation]

Thanks to @jasonvriends for initial report