Feature Request: Implement option to disable transactions

[daniellietz]

I think it would make sense to have an option called pg_use_transaction that allows you to disable transactions.

For example, in this issue: #285
not having the option to disable transactions leads to VACUUM not being possible to execute in the attached postgres database.

It could also make sense for this to only affect postgres_query() and postgres_execute(), and not the storage extension part.

I would like to implement this.

[noahisaksen]

relevant discussion duckdb/duckdb#16187

[tmontes]

(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.