transaction success when there are errors

[djouallah]

What happens?

it seems transaction commit when there is a parser error

To Reproduce

attach
or replace 'ducklake: simple.db' as simple;
use simple;
create schema if not exists test;
use simple.test;
BEGIN TRANSACTION;
SET VARIABLE list_of_files_price = NULL ;
FROM  read_csv(getvariable ('list_of_files_price'));
CREATE OR REPLACE TABLE accounts (id INTEGER, name VARCHAR, balance DECIMAL);
INSERT INTO
  accounts
VALUES
  (1, 'Alice', 1000),
  (2, 'Bob', 500);
COMMIT;
SELECT * FROM simple.test.accounts;

OS:

windows

DuckDB Version:

1.4.3

DuckLake Version:

0.3

DuckDB Client:

cli

Hardware:

No response

Full Name:

mim

Affiliation:

personal

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have not tested with any build

Did you include all relevant data sets for reproducing the issue?

No - Other reason (please specify in the issue body)

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have

[guillesd]

Hi @djouallah! This the default behaviour in DuckDB regarding transaction management when the errors happen while parsing or binding. I am aware that other systems may do this differently. I'm gonna consider this expected behaviour although please let me know if you have any arguments against this, happy to discuss!

Note that execution-time errors, like INSERT INTO t VALUES (10 / 0); will fail the transaction, but when calling COMMIT no error will be raised. This is maybe not so nice (gonna discuss this internally).

[djouallah]

I am not a database person, but I am not sure how transactions are supposed to work, my understanding postgresql will fail everything ( which I expect)

for example, just now, i run this, which worked fine the first time, but I was expecting the second run to fail, but ducklake keep adding files ?

begin transaction;
create table nation as select * from 
   parquet_scan(getvariable('root_path') || '/nation.parquet') limit 0;
CALL ducklake_add_data_files('tpch', 'nation', getvariable('root_path') || '/nation.parquet');
commit;

[guillesd]

That is a Catalog Error right? also happens while binding. I think SQLite does the same, and Indeed I think PG may do this differently. I'm asking internally but I think this is a conscious choice. This can be dealt with in a programming language though:

try:
  con.begin()
  con.sql("...")
  con.commit()
except ...:
  con.rollback()

[guillesd]

I added a need documentation tag, since this behaviour (desirable or not) should be better documented!