cockroachdb crdb - enums not working with prepared statements

[toppk]

• asyncpg version: 0.21.0
• CockroachDB version: v20.2.1
• PostgreSQL version: 12.4
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
  the issue with a local PostgreSQL install?:
• Python version: 3.9.0
• Platform: linux-x86_64
• 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?: didn't test uvloop

enums have just been added to crdb. testing asyncpg shows an issue with prepared statements.

running this:

await conn.execute("INSERT INTO accounts2 (id, balance, mode) VALUES ($1, $2, $3)", 9, 2000, "active", )
throws

    statement = await self._protocol.prepare(stmt_name, query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 163, in prepare
asyncpg.exceptions.FeatureNotSupportedError: CTEs may not be correlated

the prepared statement works fine in pgsql. enums work fine without prepared statements.

I have a silly bug exerciser, but needs some cleanup, you can see the relevant test2 functions if you need working code. i've tested psycopg2 and asyncpg against crdb and pgsql and out of the four runs, only asyncpg+crdb shows an issue.

crdbissues.py.txt

[toppk]

I've done some digging, it seems that the sql that is blowing up is the introspection that is being run. It seems that CTEs aren't fully implemented in crdb, for example:

cockroachdb/cockroach#42540

It would seem that major restructuring of the introspection would be required. I will look for a quick workaround to punch through the data that is being sought.

[toppk]

Actually, in my case it's okay not to avoid using prepared parameter for the enum type, while still using prepared parameters for the rest of the arguments. i'm using prepared statements as a security method, not for any performance improvement, so this doesn't introduce any complexity on my end.

[ale-dd]

Hitting the same issue with introspection. I'm running asyncpg v0.22.0 against cockroach v20.2.4.

[gnat]

Can replicate this same issue with Cockroach DB v21.1.5

Enums work fine until passed into a prepared statement, which results in CTEs may not be correlated exception as described by @toppk

[rafiss]

Version 21.2 of CockroachDB will support correlated CTEs (cockroachdb/cockroach#63956) -- hopefully that will help.

[lacasaprivata2]

Same happens with

  SELECT DISTINCT ON (ACCOUNT.ID) * 
  FROM ACCOUNT
  WHERE ID = ANY($1 :: uuid[])
  ORDER BY ACCOUNT.ID, ACCOUNT.TIMESTAMP DESC

[lacasaprivata2]

I think generally Cockroach isn't interoperable w/ asyncpg for non-typical queries (LEFT JOIN LATERAL, SELECT DISTINCT, etc...) from testing it on a project that require prepared staments

[rafiss]

Just to clarify, what is the error you get with the

  SELECT DISTINCT ON (ACCOUNT.ID) * 
  FROM ACCOUNT
  WHERE ID = ANY($1 :: uuid[])
  ORDER BY ACCOUNT.ID, ACCOUNT.TIMESTAMP DESC

query?