Transaction API inconsistencies

[joonnna]

The transaction API does not behave the same as manually performing a BEGIN and COMMIT statements.

use duckdb::DuckdbConnectionManager;
use r2d2::ManageConnection;

fn main() {
    let duckdb = DuckdbConnectionManager::memory().unwrap();

    let mut conn = duckdb.connect().unwrap();

    conn.execute("CREATE TABLE test(id int not null PRIMARY KEY)", [])
        .unwrap();
    conn.execute("insert into test(id) values (1)", []).unwrap();

    // This succeeds
    conn.execute_batch("BEGIN; delete from test; insert into test(id) values (1); COMMIT;")
        .unwrap();

    let tx = conn.transaction().unwrap();

    // This fails
    tx.execute("delete from test", []).unwrap();
    tx.execute("insert into test(id) values (1)", []).unwrap();

    tx.commit().unwrap();
}

Cargo toml:

[package]
name = "play"
version = "0.1.0"
edition = "2021"

# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

[dependencies]
tokio = { version = "1.37.0", features = ["full"] }
duckdb = { version = "1.1.1", features = ["r2d2", "bundled"] }
r2d2 = { version = "0.8" }

The code produces this output:

thread 'main' panicked at src/main.rs:20:56:
called `Result::unwrap()` on an `Err` value: DuckDBFailure(Error { code: Unknown, extended_code: 1 }, Some("Constraint Error: Duplicate key \"id: 1\" violates primary key constraint. If this is an unexpected constraint violation please double check with the known index limitations section in our documentation (https://duckdb.org/docs/sql/indexes)."))
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace