Document an async version of the Python client

[knz]

Raphael ‘kena’ Poss (knz) commented:

A customer came to us with the following story (paraphrased)

I copy-pasted your example Python code, and then added async and await clauses in various places. For example:

async def transfer_funds(conn: Connection, frm, to, amount):
    async with conn.transaction():
       # Check the current balance.
       from_balance = await conn.fetchval("SELECT balance FROM accounts WHERE id = '{}'".format(frm))
       if from_balance < amount:
           err_msg = "Insufficient funds in account {}: have {}, need {}".format(frm, from_balance, amount)
           raise RuntimeError(err_msg)
       # Perform the transfer.
       await conn.execute("UPDATE accounts SET balance = balance - {} WHERE id = '{}'".format(
                          amount, frm))
       await conn.execute("UPDATE accounts SET balance = balance + {} WHERE id = '{}'".format(
                          amount, to))

The resulting code "does not work" and CockroachDB complains with unimplemented: multiple active portals not supported

I advised the customer that their addition of async/await is causing their Python to issue multiple concurrent statements on the same connection object, which nternally uses multiple concurrent portals, which are not supported in crdb.

However this is not easy to understand, and we do not warn for it inside our tutorials.

I think our Python tutorials should cover uses of async/await and provide an example that works with it.

Jira Issue: DOC-535

[knz]

cc @cockroachdb/review-my-python

[bdarnell]

I advised the customer that their addition of async/await is causing their Python to issue multiple concurrent statements on the same connection object, which nternally uses multiple concurrent portals, which are not supported in crdb.

That's not true in this example; it's only executing one statement at a time. Instead, it's running in to this incompatibility between CRDB and asyncpg.

I think our Python tutorials should cover uses of async/await and provide an example that works with it.

Asynchronous python is for our purposes almost like a separate language from regular python - the drivers, orms, etc are all different so it'd be a parallel set of docs with hardly any overlap with the other python docs. It's fairly low priority for now - maybe a hello world example but I wouldn't invest in anything further.

[beedub]

Hey there. I'm the engineer referenced in the OP. This doesn't sound strictly like a CRDB issue, but I did notice that using two different query variants from asyncpg -- fetch and fetchval -- produced different outputs. Using the fetchval variant resulted in the error in the OP, while fetch executed without issue. I think this is what the lib author is referencing in the same issue at the end of the thread MagicStack/asyncpg#580 (comment), but I don't know the inner details well enough to say.

I was curious if this was because asyncpg issues different statements, but I re-ran the samples against postgres with more aggressive logging, and postgres showed the exact same logs. Perhaps my log settings weren't aggressive enough or maybe it's at the protocol level (?).

In any case, just adding some some more context just in case any other people run into the same issue.

[rafiss]

The AppDev team will plan to get to this (and give a closer look at asyncpg in general) after we finish up some work on Java and .NET tooling.

[bdarnell]

Notes for using asyncpg with CockroachCloud:

asyncpg ignores the sslrootcert options in the connection string. Instead, you must create an SSLContext:

ssl_ctx = ssl.create_default_context(cafile="cc-ca.crt")
async with asyncpg.create_pool(DB_URI, ssl=ssl_ctx) as pool:
    ...

However, it uses the system CA certificates, so this is optional if we're using a trusted certificate.

[gnat]

Eagerly awaiting developments on this topic as I'm looking to move a substantial project to cockroach on async python away from aiomysql. @bdarnell

[gnat]

Wanted to mention, you can add your user cert/key and ca cert using this:

ssl_context = ssl.create_default_context()
ssl_context.load_cert_chain(certfile="./certs/client.root.crt", keyfile="./certs/client.root.key")
ssl_context.load_verify_locations(cafile="./certs/ca.crt")
conn = await asyncpg.create_pool(
    user='root', 
    # ...
    ssl=ssl_context
)

Setting these directly as connection parameters (sslcert=, sslkey=, sslrootcert=) is due to be merged: MagicStack/asyncpg#768 @bdarnell would be a great time to write a quickstart once these changes are in.

[gnat]

Not a showstopper but this issue regarding enums and prepared statements resulting in CTEs may not be correlated is currently present: MagicStack/asyncpg#658

[rafiss]

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