Transaction expiring when materializing a table from a long running query

[klimelau]

We have been running into the following errors when using table materialization on Trino queries which ran for a certain period of time (cca over 320 seconds):
failed to commit transaction 8c6a8ab8-6458-47dc-956f-8195c33e8cc1: PrestoUserError(type=USER_ERROR, name=UNKNOWN_TRANSACTION, message="Unknown transaction ID: 8c6a8ab8-6458-47dc-956f-8195c33e8cc1. Possibly expired? Commands ignored until end of transaction block", query_id=20210811_123350_12052_t27c7)

The problematic part is the following 'main' statement block in the materialization macro:

dbt-presto/dbt/include/presto/macros/materializations/table.sql

Lines 36 to 38 in a32f92e

	{% call statement('main') -%}
	{{ create_table_as(False, intermediate_relation, sql) }}
	{%- endcall %}

As the create statement doesn't need transaction block around it, we've tried to turn it off by customizing the materialization macro by adding the "auto_begin=False" to the main statement block. This doesn't work with dbt, it complains that the transaction is not open. We were able to work around this behaviour by running the create statement before the 'main' statement block and inserting a dummy select into the block but that alters the outputs in "target/run/" folder.

We thought that the issue is related to the used Presto/Trino driver but we've tried running the code also using the dbt-trino package with trino-python-client but the output error is the same.

What could be the cause of this issue? Can it be fixed from dbt-presto side or is this affecting the dbt-core? Is there a cleaner solution to this problem than the one I described above, possibly allowing the 'main' statement block to run without transaction?

[rikturr]

I was having the same issue, and you can work around this by setting this trino setting for transaction timeouts:

transaction.idle-timeout=4h

or however long your queries run. Note that it can only be set in config.properties, not via session property

[aakashnand]

I am also facing this issue.
Is there any specific reason why transactions are explicitly started in dbt-presto even though the python client is using AUTOCOMMIT ?