Unimplemented feature for INSERT queries on ARRAY columns with CockroachDB

• asyncpg version: 0.20.1
• CockroachDB version: 19.2.6
• Python version: 3.7.4
• Platform: MacOS Mojave 10.14.6
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: No, with poetry
• Can the issue be reproduced under both asyncio and
  uvloop?: Yes

I'm using Gino as a PostgreSQL driver for CockroachDB. It doesn't work well for INSERT queries with ARRAY columns. There seems to be an introspection query executed by asyncpg when it didn't get enough knowledge about the types in the query:

https://github.com/MagicStack/asyncpg/blob/v0.20.1/asyncpg/introspection.py#L99-L134
https://github.com/MagicStack/asyncpg/blob/v0.20.1/asyncpg/connection.py#L328-L338

INFO:     INSERT INTO image (id, s3_bucket, s3_key, formats, sizes, created_at) VALUES ($1, $2, $3, $4, $5, $6) RETURNING image.id, image.s3_bucket, image.s3_key, image.formats, image.sizes, image.created_at
INFO:     ('2941f3a0-e2ea-4d9d-8e23-2ce7b793bcac', 'testbucket', 'iqEb1mDimiUpRsmG', ['jpeg'], [], 1588471074)
INFO:     COMMIT
ERROR:    Exception in ASGI application
Traceback (most recent call last):
...
    created_obj = await cls(**kwargs).create()
  File "/Users/son/Library/Caches/pypoetry/virtualenvs/project-rain-7LNSaARJ-py3.7/lib/python3.7/site-packages/gino/crud.py", line 485, in _create
    row = await bind.first(q)
  File "/Users/son/Library/Caches/pypoetry/virtualenvs/project-rain-7LNSaARJ-py3.7/lib/python3.7/site-packages/gino/engine.py", line 748, in first
    return await conn.first(clause, *multiparams, **params)
  File "/Users/son/Library/Caches/pypoetry/virtualenvs/project-rain-7LNSaARJ-py3.7/lib/python3.7/site-packages/gino/engine.py", line 328, in first
    return await result.execute(one=True)
  File "/Users/son/Library/Caches/pypoetry/virtualenvs/project-rain-7LNSaARJ-py3.7/lib/python3.7/site-packages/gino/dialects/base.py", line 215, in execute
    context.statement, context.timeout, args, 1 if one else 0
  File "/Users/son/Library/Caches/pypoetry/virtualenvs/project-rain-7LNSaARJ-py3.7/lib/python3.7/site-packages/gino/dialects/asyncpg.py", line 184, in async_execute
    result, stmt = await getattr(conn, "_do_execute")(query, executor, timeout)
  File "/Users/son/Library/Caches/pypoetry/virtualenvs/project-rain-7LNSaARJ-py3.7/lib/python3.7/site-packages/asyncpg/connection.py", line 1423, in _do_execute
    stmt = await self._get_statement(query, None)
  File "/Users/son/Library/Caches/pypoetry/virtualenvs/project-rain-7LNSaARJ-py3.7/lib/python3.7/site-packages/asyncpg/connection.py", line 338, in _get_statement
    types_with_missing_codecs, timeout)
  File "/Users/son/Library/Caches/pypoetry/virtualenvs/project-rain-7LNSaARJ-py3.7/lib/python3.7/site-packages/asyncpg/connection.py", line 378, in _introspect_types
    self._intro_query, (list(typeoids),), 0, timeout)
  File "/Users/son/Library/Caches/pypoetry/virtualenvs/project-rain-7LNSaARJ-py3.7/lib/python3.7/site-packages/asyncpg/connection.py", line 1411, in __execute
    return await self._do_execute(query, executor, timeout)
  File "/Users/son/Library/Caches/pypoetry/virtualenvs/project-rain-7LNSaARJ-py3.7/lib/python3.7/site-packages/asyncpg/connection.py", line 1423, in _do_execute
    stmt = await self._get_statement(query, None)
  File "/Users/son/Library/Caches/pypoetry/virtualenvs/project-rain-7LNSaARJ-py3.7/lib/python3.7/site-packages/asyncpg/connection.py", line 328, in _get_statement
    statement = await self._protocol.prepare(stmt_name, query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 163, in prepare
asyncpg.exceptions.FeatureNotSupportedError: at or near "select": syntax error: unimplemented: this syntax
DETAIL:  source SQL:
WITH RECURSIVE typeinfo_tree(
    oid, ns, name, kind, basetype, has_bin_io, elemtype, elemdelim,
    range_subtype, elem_has_bin_io, attrtypoids, attrnames, depth)
AS (
    SELECT
        ti.oid, ti.ns, ti.name, ti.kind, ti.basetype, ti.has_bin_io,
        ti.elemtype, ti.elemdelim, ti.range_subtype, ti.elem_has_bin_io,
        ti.attrtypoids, ti.attrnames, 0
    FROM
            (
        SELECT
            t.oid                           AS oid,
            ns.nspname                      AS ns,
            t.typname                       AS name,
            t.typtype                       AS kind,
            (CASE WHEN t.typtype = 'd' THEN
                (WITH RECURSIVE typebases(oid, depth) AS (
                    SELECT
                        t2.typbasetype      AS oid,
                        0                   AS depth
                    FROM
                        pg_type t2
                    WHERE
                        t2.oid = t.oid

                    UNION ALL

                    SELECT
                        t2.typbasetype      AS oid,
                        tb.depth + 1        AS depth
                    FROM
                        pg_type t2,
                        typebases tb
                    WHERE
                       tb.oid = t2.oid
                       AND t2.typbasetype != 0
               ) SELECT oid FROM typebases ORDER BY depth DESC LIMIT 1)
                 ^
HINT:  You have attempted to use a feature that is not yet implemented.
See: https://github.com/cockroachdb/cockroach/issues/21085

Commencez par la requête d’introspection dans asyncpg/introspection.py autour des lignes 99–134 et par le traitement des instructions dans asyncpg/connection.py autour des lignes 328–338. Reproduisez l’INSERT avec des colonnes ARRAY contre CockroachDB et suivez la requête récursive de types qui échoue ; c’est terminé lorsqu’un comportement compatible est établi ou que la limitation est documentée avec une couverture de régression.