relevant discussion duckdb/duckdb#16187

(chiming in with an additional perspective)

I've very recently bumped into this limitation. The context is an ETL-like dbt-duckdb mini-project that:

• Reads Parquet data from S3.
• Computes aggregates.
• Writes results to a PostgreSQL DB.

I wanted to add a dbt post_hook to trigger a PostgreSQL VACUUM ANALYZE, but it failed due to the underlying in progress transaction.

My contribution/request is, thus, two-fold:

• I myself would find such an option very useful.
• It would be lovely if it could be handled in a way that is easily leveraged from within a dbt hook.

Maybe instead of an option, we could have something like CALL postgres_execute('psql', 'VACUUM ANALYZE table', transaction=false) work? This would be a single DuckDB statement with a behaviour along the lines of:

• Commit the outstanding transaction.
• Execute the given SQL statement, VACUUM in this case.
• Begin a new transaction.

My 2c.

We currently have the same issue as @tmontes. It would be great to have an option to bypass transactions. Our solution is currently to call another service to do this.

We are blocked on same issue for a dbt post-hook. Cannot update a postgres table while in a duckdb transaction. Having ability to disable PG transaction would be very very helpful.