Support cockroachDB

[DoubleDi]

Hi @long2ice! We are using cockroachDB with a postgresql API with tortoise. Unfortunately the current version of upgrade command does fail with this error.

Traceback (most recent call last):
  File "/usr/local/bin/aerich", line 8, in <module>
    sys.exit(main())
  File "/usr/local/lib/python3.9/site-packages/aerich/cli.py", line 258, in main
    cli()
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1137, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1062, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1668, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 763, in invoke
    return __callback(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/click/decorators.py", line 26, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/aerich/cli.py", line 33, in wrapper
    loop.run_until_complete(f(*args, **kwargs))
  File "/usr/local/lib/python3.9/asyncio/base_events.py", line 642, in run_until_complete
    return future.result()
  File "/usr/local/lib/python3.9/site-packages/aerich/cli.py", line 102, in upgrade
    migrated = await command.upgrade()
  File "/usr/local/lib/python3.9/site-packages/aerich/__init__.py", line 54, in upgrade
    await Aerich.create(
  File "/usr/local/lib/python3.9/site-packages/tortoise/backends/base/client.py", line 264, in __aexit__
    await self.connection.commit()
  File "/usr/local/lib/python3.9/site-packages/tortoise/backends/asyncpg/client.py", line 235, in commit
    await self.transaction.commit()
  File "/usr/local/lib/python3.9/site-packages/asyncpg/transaction.py", line 211, in commit
    await self.__commit()
  File "/usr/local/lib/python3.9/site-packages/asyncpg/transaction.py", line 179, in __commit
    await self._connection.execute(query)
  File "/usr/local/lib/python3.9/site-packages/asyncpg/connection.py", line 315, in execute
    return await self._protocol.query(query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 338, in query
asyncpg.exceptions.FeatureNotSupportedError: unimplemented: multiple active portals not supported
HINT:  You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/40195/v21.1

It seems, that multiple active portals (open cursors) are currently not supported. Is it possible to tweak the migrations, to avoid it. It would make tortoise fully compatible to cockcroachDB.

[long2ice]

Maybe problem of asyncpg

[gnat]

This can be fixed by using fetch() instead of its variations (fetchval() ..) when using asyncpg.

This is likely the problematic line: https://github.com/tortoise/tortoise-orm/blob/aa44d5a28737f08c39e98a94f0d741139ecb5fdb/tortoise/backends/asyncpg/client.py#L108

Just something I've worked around in my own library, but was checking out Tortoise.

[gnat]

Actually, seems like CockroachDB works on latest versions of everything. Using aerich migrations right now.

@DoubleDi are you still experiencing the "multiple active portals" issue?

[gnat]

Just a heads up, ideally Cockroach DB support would have USING HASH on sequential columns (Ex: SERIAL NOT NULL PRIMARY KEY), unless of course this makes it in: https://github.com/cockroachdb/cockroach/issues/78049

Currently the migrator does not work with hash sharded indexes on Cockroach, sadly.