pgpro_axe supports multi-statement transactions. It is currently required to ensure the expected ACID guarantees: you cannot write to both a Postgres Pro table and a DuckDB table in the same transaction.

Similarly, you can do DDL (such as CREATE/DROP TABLE) on DuckDB tables inside a transaction, but it is not allowed to combine such statements with DDL involving Postgres Pro objects.

This restriction can be completely disabled to allow writes to both DuckDB and Postgres Pro in the same transaction by setting duckdb.unsafe_allow_mixed_transactions to true. However, it is not recommended to do so, as it can result in the transaction being committed only in DuckDB, but not in Postgres Pro. This can lead to inconsistencies and data loss.

  BEGIN;
  SET LOCAL duckdb.unsafe_allow_mixed_transactions TO true;
  CREATE TABLE pg_table AS SELECT * FROM duckdb_table;
  DROP TABLE duckdb_table;
  COMMIT;