Repository navigation
Sqlalchemy transactions with Repeatable_read #481
Description
Expected behavior
dding a isolation level in the SA Session causes a failure when using any of the levels with underscores.
Trino client is removing the underscore, but then SA requires the underscore in downstream methods for a enum lookup.
Looking at the tests, it seems like it was not tested against the isolation levels with underscores.
Thanks.
Actual behavior
Fails
Steps To Reproduce
Use isolation level REPEATABLE_READ with SA
Log output
No response
Operating System
Windoss
Trino Python client version
Latest
Trino Server version
429
Python version
3.9
Are you willing to submit PR?
- Yes I am willing to submit a PR!
Activity
hashhar commentedon Sep 6, 2024
Thank you for the report and being willing to submit a PR. I'll take a look at the PR once it's available.
StephenOTT commentedon Sep 6, 2024
Hey @hashhar can you confirm for me: are transactions supported in the Postgres connector?
The docs seem to allude yes, but some ~random issues in this repo seem to indicate no.
hashhar commentedon Sep 9, 2024
No. Transactions are emulated for the Hive connector only as far as I remember.
cc: @mosabua Maybe we should clarify this in the docs + check what's the long term plan around transactions - will they go away eventually since we can only try to "emulate" them?
StephenOTT commentedon Sep 11, 2024
At the very least to add a big warning on this repos readme in the transaction section. :)
ChrisGVE commentedon Jul 30, 2026
Still present in 0.338.0 against Trino 476, and I can add the exact mechanism plus a second symptom nobody has mentioned here yet.
Versions: trino 0.338.0 (
trino[sqlalchemy]), SQLAlchemy 2.0.51, Python 3.12.9, Trino 476 in Docker.SQLAlchemy normalises an isolation level to spaces before handing it over; the dialect looks it up in an enum keyed with underscores (
IsolationLevel.READ_UNCOMMITTED). So every level whose name is two words raises at connect time, and the one-word one matches by accident:SERIALIZABLEREAD UNCOMMITTEDKeyError: 'READ UNCOMMITTED'at connectREAD COMMITTEDKeyError: 'READ COMMITTED'at connectREPEATABLE READKeyError: 'REPEATABLE READ'at connectThe practical consequence is worth spelling out, because it isn't obvious from the title:
SERIALIZABLEis not a strictness choice, it's the only level reachable at all. Anyone who wants a transaction and doesn't care about the snapshot — which is my case, I only need writes to be undoable — is silently forced onto the strictest level. Once this is fixed the weakest level becomes reachable and is the better default for that use.Second symptom, separate from the enum lookup:
isolation_levelis ignored as a URL query parameter. Passing it in the URL leaves the connection reportingAUTOCOMMIT, with no error and no warning. It only takes effect viacreate_engine(..., isolation_level=...). That one bit me first and cost more time than theKeyError, because aKeyErrorat least tells you something happened.Happy to test a fix against my setup if that's useful.