List[str] parameter treated as text instead of text[] in certain requests

Chạy hai bản tái hiện try_it() trên asyncpg 0.27.0 và PostgreSQL 15.1, so sánh chúng với các phép ép kiểu ::text[] tường minh. Theo dõi cách các tham số List được suy luận và mã hóa cho các truy vấn VALUES và unnest. Hoàn tất có nghĩa là cả hai ví dụ đều coi danh sách là text[] mà không yêu cầu các phép ép kiểu SQL, với kiểm thử hồi quy bao phủ các trường hợp đã được báo cáo.

• asyncpg version: 0.27.0
• PostgreSQL version: 15.1
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
  the issue with a local PostgreSQL install?: No
• Python version: 3.8.10
• Platform: Ubuntu
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: Yes
• If you built asyncpg locally, which version of Cython did you use?:
• Can the issue be reproduced under both asyncio and
  uvloop?: Yes

I'm working with a custom bulk operations API built on top of SQLAlchemy and asyncpg, and some scenarios the List parameters are not treated as postgres arrays.

Example 1:

import asyncio
import asyncpg

async def try_it(table: str, query: str, *params, **connargs):
   conn = await asyncpg.connect(**connargs)
   try:
     await conn.execute(f'CREATE TABLE test_table({table});')
     await conn.execute(query, *params)
   finally:
     await conn.execute('DROP TABLE test_table;')
     await conn.close()

table = 'a text[], b text'
query = """
UPDATE test_table SET a = uvals.a
FROM (VALUES ($1, $2)) AS uvals (a, b)
WHERE test_table.b = uvals.b
"""
params = [ ['hello', 'world'], 'helloworld']

db_conn_params = {}
asyncio.get_event_loop().run_until_complete(try_it(table, query, *params, **db_conn_params))

Response:

asyncpg.exceptions.DatatypeMismatchError: column "a" is of type text[] but expression is of type text

Example 2:

# same imports and try_it() from above

table = 'a text, b int'
query = """
SELECT a, b
FROM test_table
UNION
  SELECT
    values as a,
    5 as b
  FROM unnest($1) as values
"""
params = [ ['hello', 'world'] ]

# execution as above

Response:

asyncpg.exceptions.AmbiguousFunctionError: function unnest(unknown) is not unique

Adding a $1 :: text[] solves the problem in each case, since it appears to be passing the list as text but there are scenarios where I don't have direct control of the SQL (it being auto-generated).