asyncpg.exceptions.UndefinedFunctionError: function pg_listening_channels() does not exist

[shuai93]

this is error message

Traceback (most recent call last):
  File "/home/gt/workspace/algo_syncer/base/async_pg.py", line 81, in <module>
    loop.run_until_complete(run_query('SELECT 1'))
  File "/usr/lib/python3.6/asyncio/base_events.py", line 473, in run_until_complete
    return future.result()
  File "/home/gt/workspace/algo_syncer/base/async_pg.py", line 65, in run_query
    await pool.release(con)
  File "/home/gt/.local/lib/python3.6/site-packages/asyncpg/pool.py", line 643, in release
    return await asyncio.shield(ch.release(timeout), loop=self._loop)
  File "/home/gt/.local/lib/python3.6/site-packages/asyncpg/pool.py", line 214, in release
    raise ex
  File "/home/gt/.local/lib/python3.6/site-packages/asyncpg/pool.py", line 204, in release
    await self._con.reset(timeout=budget)
  File "/home/gt/.local/lib/python3.6/site-packages/asyncpg/connection.py", line 1115, in reset
    await self.execute(reset_query, timeout=timeout)
  File "/home/gt/.local/lib/python3.6/site-packages/asyncpg/connection.py", line 273, in execute
    return await self._protocol.query(query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 316, in query
asyncpg.exceptions.UndefinedFunctionError: function pg_listening_channels() does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

and this is my python3.6 code

import asyncpg
import asyncio
import traceback


dsn  = 'postgres://gpadmin:[email protected]:2345/algo'

async def run_query(query):

    pool = await asyncpg.create_pool(dsn, command_timeout=60)
    con = await pool.acquire()

    try:
        if query.startswith('delete from') or query.startswith('insert'):
            result = await con.execute(query)
        else:
            result = await con.fetchval(query)
    except:
        print('Could not complete query "{}"'.format(query))
        print(traceback.format_exc())
        result = None
        exit(1)
    finally:
        await pool.release(con)
    return result


if __name__ == '__main__':
    
    loop = asyncio.get_event_loop()
    loop.run_until_complete(run_query('SELECT 1'))

Please tell me what can i do next

[elprans]

When submitting an issue, please provide the information asked for in the issue template. Specifically, which version of PostgreSQL are you using?

[shuai93]

Yeah, The following is my relevant version information.

My database relevent info is PostgreSQL 8.3.23 (Greenplum Database 5.10.2 build commit:b3c02f3acd880e2d676dacea36be015e4a3826d4) on x86_64-pc-linux-gnu, compiled by GCC gcc (GCC) 6.2.0, 64-bit compiled on Aug 10 2018 07:30:09 ; and my python package info

aiopg                             0.14.0     
asyncpg                           0.18.2     
psycopg2                          2.7.4      
psycopg2-binary                   2.7.4      
unattended-upgrades               0.1  

[elprans]

We don't really support PostgreSQL versions that old. In this particular case, pg_listening_channels() is a system function introduced in PostgreSQL 9.0. It seems that earlier versions relied on the pg_listener system catalog for the same purpose. You can add a version check to _detect_server_capabilities and write the listener information source to ServerCapabilities, i.e

ServerCapabilities(
    listener_source='pg_listening_channels()' if server_version >= (9, 0) 
                    else 'pg_listener')

and use that in _get_reset_query.

[healiseu]

Hi, it seems I have run into a similar problem.

pgcrypto 1.3 psql (PostgreSQL) 13.3 asyncpg 0.23.0

asyncpg.exceptions.UndefinedFunctionError: function gen_salt(unknown) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

In my case that is a postgresql-extension schema problem, i.e. how you create the extension and how you are calling its function. So, the solution I found was:

1. Create extension

CREATE EXTENSION pgcrypto SCHEMA "foo"

2. Call the function by prepending the schema

q = f"select foo.crypt($1, foo.gen_salt('bf')) as pwd"
await connection.fetchval(q, '123')

Hope that helps, it works in my case.