Problems with async or pooling using asyncpg

[MatsGej]

What is the bug or the crash?

Getting error asyncpg.exceptions.UndefinedFunctionError: operator does not exist: text %% text HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

Steps to reproduce the issue

This is my code for initiating the connection:
conn_str = f'postgresql://{user}:{password}@{host}:{port}/{dbname}'

# Create a new connection when your application starts

conn = await asyncpg.connect(conn_str)

and this is the code for using that connection:

async def fuzzy_search(conn, search_term, language):
try:
# Determine the column to search based on the language
column = 'name_en' if language == 'en' else 'name_se'

    # Create the SQL query
    query = f"SELECT DISTINCT *, similarity({column}::text, $1) AS score FROM places WHERE {column}::text %% $1 ORDER BY score DESC"

    # Execute the query
    rows = await conn.fetch(query, search_term)

    if rows == []:
        return None

    places = []
    for row in rows:
        place = extract_place(row, language)
        places.append(place)

    return places

except Exception as e:
    print(f"Error in fuzzy_search: {e}, search_term:{search_term}")
    traceback.print_exc()  # This will print the full stack trace
    return None

The database has been enabled for the pg_trgm extension

Versions

kartoza/postgis:11.0-2.5

Additional context

The database has been loaded with lots of geographical places and works fine with synchronous calls.

Have tried both with async and with pooling and both combined.

Have just had a 2-3 hours session with github chat trying to find a solution. But it gave up.

[NyakudyaA]

Image 11 is way down the release order and I doubt you will get much help debugging this from the community. Have you tried to at least use the latest image or better update the 11 image to the one that uses postgis3?

[MatsGej]

I updated to the latest version (pulled it from docker hub) and I still have exactly the same problem

[NyakudyaA]

I updated to the latest version (pulled it from docker hub) and I still have exactly the same problem

This looks like some missing function. Can you please produce a sample dataset i.e something reproducible for use to test

[MatsGej]

I have coded a synchronous version and an asynch version
The synch version works fine, the async does not
First the synch version:
I setup a connection this way
wikidata_conn = psycopg2.connect(conn_str)
pools.update({"wikidata_conn": wikidata_conn})
and then used it this way
def sync_fuzzy_search(pools, name, language):
try:
# Determine the column to search based on the language
column = 'name_en' if language == 'en' else 'name_se'

    # Create the SQL query
    query = f"SELECT DISTINCT *, similarity({column}, %s) AS score FROM places WHERE {column} %% %s ORDER BY score DESC"

    conn = pools["wikidata_conn"]
    with conn.cursor() as cur:
        cur.execute(query, (name, name))  # Use %s as placeholder
        row = cur.fetchone()
        place = extract_place(row, language) if row else None  # Pass the JSON object to extract_place
        return place
except Exception as e:
    print(f"Exception in sync_fuzzy_search: {name} {e}")
    traceback.print_exc()
    return None

It works fine.

And then the async version:
Setup:
wikidata_pool = await asyncpg.create_pool(database=dbname, user=user, password=password, host=host, port=port)
pools.update({"wikidata_pool": wikidata_pool})

Execution:
def async_fuzzy_search(pools, search_term, language):
try:
# Determine the column to search based on the language
column = 'name_en' if language == 'en' else 'name_se'

    # Create the SQL query
    query = f"SELECT DISTINCT *, similarity({column}, $1) AS score FROM places WHERE {column} %% $1 ORDER BY score DESC"

    # Execute the query
    rows = await pools["wikidata_pool"].fetch(query, search_term)

    if rows == []:
        return None

    places = []
    for row in rows:
        place = extract_place(row, language)
        places.append(place)

    return places

But here it does not like %%. Get this error:
asyncpg.exceptions.UndefinedFunctionError: operator does not exist: text %% text HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

[MatsGej]

I have another postgresql functions which is using asyncpg and they work fine. It is just the fuzzy search not working.