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

在 asyncpg 0.27.0 和 PostgreSQL 15.1 上运行两个 try_it() 复现，并将它们与显式的 ::text[] 类型转换进行比较。跟踪 VALUES 和 unnest 查询中 List 参数的推断和编码方式。完成标准是两个示例都将列表视为 text[]，无需 SQL 类型转换，并且为所报告的案例提供回归测试覆盖。

• 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).