Piccolo fails when underlying DB isn't 100% postgres-compatible

[waldner]

It seems to me that piccolo always runs SHOW server_version and tries to fetch/create extensions on startup, with no way to disable these actions.

Now I happen to be trying to run piccolo using crateDB as backend, which claims to be mostly compatible with postgres (see eg https://cratedb.com/docs/crate/reference/en/latest/interfaces/postgres.html or https://cratedb.com/docs/crate/reference/en/latest/appendices/compatibility.html). However, it looks like certain postgres features are not supported:

/usr/lib/python3.12/asyncio/events.py:88: Warning: Unable to fetch server version: 'server_version'
  self._context.run(self._callback, *self._args)
ERROR:devices:Unable to connect to the database: line 1:8: no viable alternative at input 'CREATE EXTENSION'

Apart from this, the application then runs normally. The problem comes when running piccolo from the command line, as any command crashes it, for example:

$ piccolo migrations --help
/usr/lib/python3.12/asyncio/events.py:88: Warning: Unable to fetch server version: 'server_version'
  self._context.run(self._callback, *self._args)
Traceback (most recent call last):
  File "/home/waldner/venv/bin/piccolo", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/home/waldner/venv/lib/python3.12/site-packages/piccolo/main.py", line 87, in main
    APP_REGISTRY: AppRegistry = Finder().get_app_registry()
                                ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/waldner/venv/lib/python3.12/site-packages/piccolo/conf/apps.py", line 423, in get_app_registry
    piccolo_conf_module = self.get_piccolo_conf_module()
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/waldner/venv/lib/python3.12/site-packages/piccolo/conf/apps.py", line 392, in get_piccolo_conf_module
    module = t.cast(PiccoloConfModule, import_module(module_name))
                                       ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.12/importlib/__init__.py", line 90, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<frozen importlib._bootstrap>", line 1387, in _gcd_import
  File "<frozen importlib._bootstrap>", line 1360, in _find_and_load
  File "<frozen importlib._bootstrap>", line 1331, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 935, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 995, in exec_module
  File "<frozen importlib._bootstrap>", line 488, in _call_with_frames_removed
  File "/home/waldner/projects/firstservice/piccolo_conf.py", line 6, in <module>
    DB = PostgresEngine(
         ^^^^^^^^^^^^^^^
  File "/home/waldner/venv/lib/python3.12/site-packages/piccolo/engine/postgres.py", line 374, in __init__
    super().__init__(
  File "/home/waldner/venv/lib/python3.12/site-packages/piccolo/engine/base.py", line 105, in __init__
    run_sync(self.prep_database())
  File "/home/waldner/venv/lib/python3.12/site-packages/piccolo/utils/sync.py", line 19, in run_sync
    return asyncio.run(coroutine)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.12/asyncio/runners.py", line 194, in run
    return runner.run(main)
           ^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.12/asyncio/runners.py", line 118, in run
    return self._loop.run_until_complete(task)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.12/asyncio/base_events.py", line 687, in run_until_complete
    return future.result()
           ^^^^^^^^^^^^^^^
  File "/home/waldner/venv/lib/python3.12/site-packages/piccolo/engine/postgres.py", line 419, in prep_database
    await self._run_in_new_connection(
  File "/home/waldner/venv/lib/python3.12/site-packages/piccolo/engine/postgres.py", line 529, in _run_in_new_connection
    raise exception
  File "/home/waldner/venv/lib/python3.12/site-packages/piccolo/engine/postgres.py", line 526, in _run_in_new_connection
    results = await connection.fetch(query, *args)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/waldner/venv/lib/python3.12/site-packages/asyncpg/connection.py", line 690, in fetch
    return await self._execute(
           ^^^^^^^^^^^^^^^^^^^^
  File "/home/waldner/venv/lib/python3.12/site-packages/asyncpg/connection.py", line 1864, in _execute
    result, _ = await self.__execute(
                ^^^^^^^^^^^^^^^^^^^^^
  File "/home/waldner/venv/lib/python3.12/site-packages/asyncpg/connection.py", line 1961, in __execute
    result, stmt = await self._do_execute(
                   ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/waldner/venv/lib/python3.12/site-packages/asyncpg/connection.py", line 2004, in _do_execute
    stmt = await self._get_statement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/waldner/venv/lib/python3.12/site-packages/asyncpg/connection.py", line 432, in _get_statement
    statement = await self._protocol.prepare(
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "asyncpg/protocol/protocol.pyx", line 165, in prepare
asyncpg.exceptions.InternalServerError: line 1:8: no viable alternative at input 'CREATE EXTENSION'

Aside from the fact that I don't understand why piccolo must try to connect to the db even if a simple --help on the syntax is requested...but is there any way to make this work?

Note that I'm stumbling upon this with crateDB, but it wouldn't surprise me if other "postgres-compatible" engines (like RDS, Redshift, Hydra etc) failed in the same or similar way.

[waldner]

Ok, as it happens, I've found the extensions=() argument to the PostgresEngine constructor just after opening the issue. This way I'm able to at least run piccolo, still leaving this open in case there's any other suggestion/advice to deal with these cases.

[dantownsend]

Sorry for the slow reply. You're right, the extensions argument can be used to stop Piccolo trying to enable the extensions.

When PostgresEngine is instantiated, we try to make sure the Postgres version is supported. I'm not sure how useful of a feature this is, because like you say, it means we do it ever time we run a CLI command.

[waldner]

Due to further incompatibilities/missing features in crateDB I've since switched to a different, more compatible backend. Thanks!