Fix dbt-trino transaction timeout on queries longer than 5 minutes

Fix dbt-trino transaction timeout on queries longer than 5 minutes

dbt runs failed on Trino whenever a query ran longer than 5 minutes, because dbt-trino's transaction handling conflicted with Trino's default transaction.idle-timeout of 5 minutes (the adapter opened a transaction in one request while the query ran in another, then timed out at commit). The root cause was in the transaction handling of the Trino python client path used by dbt-trino. It was fixed in dbt-trino PR #30, which removed the no-op start_transaction/commit/rollback calls since the adapter runs in auto-commit mode. Upgrade dbt-trino past that merge and long-running models stop timing out.

How to handle it

Any dbt model taking over 5 minutes on Trino failed at commit time. The thread dug in and found dbt-trino was opening transactions that did nothing (auto-commit mode already commits each query), and Trino's 5-minute transaction.idle-timeout killed them. dbt-trino PR #30 removed the no-op transaction calls, which the maintainer merged. Upgrade dbt-trino to a release containing that fix and your long seeds/models complete. If you cannot upgrade, raising transaction.idle-timeout on the Trino server is the workaround, but it only masks the bug. Source: https://github.com/starburstdata/dbt-trino/issues/20

Question asked in the thread: dbt runs failed on Trino whenever a query ran longer than 5 minutes, because dbt-trino's transaction handling conflicted with Trino's default transaction.idle-timeout of 5 minutes (the adapter opened a transaction in one request while the query ran in another, then timed out at commit). The root cause was in the transaction handling of the Trino python client path used by dbt-trino. It was fixed in dbt-trino PR #30, which removed the no-op start_transaction/commit/rollback calls since the adapter runs in auto-commit mode. Upgrade dbt-trino past that merge and long-running models stop timing out.

Verified answer: Any dbt model taking over 5 minutes on Trino failed at commit time. The thread dug in and found dbt-trino was opening transactions that did nothing (auto-commit mode already commits each query), and Trino's 5-minute transaction.idle-timeout killed them. dbt-trino PR #30 removed the no-op transaction calls, which the maintainer merged. Upgrade dbt-trino to a release containing that fix and your long seeds/models complete. If you cannot upgrade, raising transaction.idle-timeout on the Trino server is the workaround, but it only masks the bug. Source: https://github.com/starburstdata/dbt-trino/issues/20

Thread: https://vectle.com/threads/thr_sMzjLRe1Ic0Wnlkk63wYLg