Cockroachdb connection pooling

[kszucs]

• asyncpg version: 0.9.0
• PostgreSQL version: Cockroachdb beta-20170223
• Python version: Python 3.6.0
• Platform: latest OS X
• Do you use pgbouncer?: no
• Did you install asyncpg with pip?: yes
• If you built asyncpg locally, which version of Cython did you use?: 0.25.2
• Can the issue be reproduced under both asyncio and
  uvloop?: not related

I'm trying to use cockroachdb with asyncpg.

With plain connection it works like a charm. On the other hand using connection pool fails with the following error:

ERROR:sanic:Traceback (most recent call last):
  File "/Users/krisz/.pyenv/versions/miniconda3-latest/envs/ml3/lib/python3.6/site-packages/sanic/app.py", line 391, in handle_request
    response = await response
  File "/Users/krisz/Workspace/papi/papi/app.py", line 34, in accounts_create
    print(results)
  File "/Users/krisz/.pyenv/versions/miniconda3-latest/envs/ml3/lib/python3.6/site-packages/asyncpg/pool.py", line 257, in __aexit__
    await self.pool.release(con)
  File "/Users/krisz/.pyenv/versions/miniconda3-latest/envs/ml3/lib/python3.6/site-packages/asyncpg/pool.py", line 189, in release
    await connection.reset()
  File "/Users/krisz/.pyenv/versions/miniconda3-latest/envs/ml3/lib/python3.6/site-packages/asyncpg/connection.py", line 412, in reset
    ''')
  File "/Users/krisz/.pyenv/versions/miniconda3-latest/envs/ml3/lib/python3.6/site-packages/asyncpg/connection.py", line 171, in execute
    return await self._protocol.query(query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 255, in query (asyncpg/protocol/protocol.c:56799)
asyncpg.exceptions.InternalServerError: syntax error at or near "DO"

Caused by the resetting the connection :

await self.execute('''
            DO $$
            BEGIN
                PERFORM * FROM pg_listening_channels() LIMIT 1;
                IF FOUND THEN
                    UNLISTEN *;
                END IF;
            END;
            $$;
            SET SESSION AUTHORIZATION DEFAULT;
            RESET ALL;
            CLOSE ALL;
            SELECT pg_advisory_unlock_all();
        ''')

I don't know the exact solution, but I'd like to use Cockroach with asyncpg :)

I've created an issue for cockroach developers too cockroachdb/examples-orms#19

[kszucs]

UPDATE: Cockroach doesn't support plpgsql. Is there a way to use asyncpg's connection pooling without plpgsql scripts?

[elprans]

We could put a check there if cocroach distinguishes itself somehow through its version string. What does connection.get_server_version() return?

[kszucs]

ServerVersion(major=9, minor=5, micro=0, releaselevel='final', serial=0)

[elprans]

That seems like a stock Postgres response. We need some way to tell that it's not Postgres on the other end.

[kszucs]

cc @knz @tamird

[kszucs]

I couldn't find suitable statement: https://www.cockroachlabs.com/docs/sql-statements.html
Here is their sqlalchemy adapter/dialect

How about explicitly telling to the pool with a parameter?

[knz]

Also perhaps cockroachdb/cockroach#14142 would help? (once solved)

[elprans]

How about explicitly telling to the pool with a parameter?

I don't think it's a good idea.

[elprans]

Also perhaps cockroachdb/cockroach#14142 would help? (once solved)

Yes, anything on SQL level that does not raise an error under Postgres would be fine.

[elprans]

@knz Ideally, though, Cocroach should send something in the ParameterStatus message (in addition to server_version).

[knz]

@elprans do you reckon (other) clients would be confused by receiving a parameterstatus key that's not provided by pg otherwise?

[elprans]

@knz I doubt that. Postgres itself adds new keys periodically: https://www.postgresql.org/docs/current/static/protocol-flow.html#PROTOCOL-ASYNC

[elprans]

Relevant excerpt:

This set might change in the future, or even become configurable. Accordingly, a frontend should simply ignore ParameterStatus for parameters that it does not understand or care about.

[knz]

FYI cockroachdb/cockroach#14145

[kszucs]

Great! 👍 Really appreciate your work!

[knz]

The ParameterStatus is crdb_version -- you'll get it if you build CockroachDB from source now, or wait until the next beta release later this week.

[kszucs]

@elprans I've built a docker image kszucs/cockroach:20170314

[kszucs]

Great, thanks!