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.