I ran into an issue with using this package with my multi bind session. I am doing something like this:
async_engines = {
DatabaseType.POSTGRES: create_async_engine(
str(config.ASYNC_DATABASE_URL), **db_engine_params
),
DatabaseType.CLICKHOUSE: create_async_engine(
str(config.CLICKHOUSE_URL),
**db_engine_params,
execution_options={"final": True},
),
}
SessionMaker = async_sessionmaker(
binds={
PostgresBase: self.async_engines[DatabaseType.POSTGRES],
ClickHouseBase: self.async_engines[DatabaseType.CLICKHOUSE],
},
**sessionmaker_params,
)
# Simple select operations work fine
result = await session.execute(select(...))
# But this fails:
await session.commit()
File "/venv/lib/python3.13/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 132, in await_only
return current.parent.switch(awaitable) # type: ignore[no-any-return,attr-defined] # noqa: E501
~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^
File "/venv/lib/python3.13/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 196, in greenlet_spawn
value = await result
^^^^^^^^^^^^
File "/venv/lib/python3.13/site-packages/asynch/connection.py", line 181, in commit
raise NotSupportedError
asynch.errors.NotSupportedError: Code: None.
Root Cause
The error originates from the asynch library's connection implementation, specifically in this code section, where commit() and rollback() operations raise NotSupportedError.
Current Workaround
I've temporarily modified my code to use the multi-bind session only for read operations, requiring separate sessions for Clickhouse write operations. This is not perfect as it breaks the unified session pattern.
Proposed Solution
Since Clickhouse doesn't support traditional transactions, these operations (commit() and rollback()) should be implemented as no-ops in the SQLAlchemy context. This would allow multi-bind sessions to work seamlessly.
Work in Progress
I've started working on a solution in this commit. I'm happy to complete these changes by adding tests and submit a PR with tests if this approach is acceptable.
I ran into an issue with using this package with my multi bind session. I am doing something like this:
Root Cause
The error originates from the asynch library's connection implementation, specifically in this code section, where commit() and rollback() operations raise NotSupportedError.
Current Workaround
I've temporarily modified my code to use the multi-bind session only for read operations, requiring separate sessions for Clickhouse write operations. This is not perfect as it breaks the unified session pattern.
Proposed Solution
Since Clickhouse doesn't support traditional transactions, these operations (commit() and rollback()) should be implemented as no-ops in the SQLAlchemy context. This would allow multi-bind sessions to work seamlessly.
Work in Progress
I've started working on a solution in this commit. I'm happy to complete these changes by adding tests and submit a PR with tests if this approach is acceptable.