Inconsistent enforcement of primary key constraints in transaction

[akbertram]

What does happen?
When inserting, deleting, and then inserting again a row with a single primary key within a transaction, the final statement results in:

Error: Constraint Error: PRIMARY KEY or UNIQUE constraint violated: duplicated key

What should happen?
Primary key enforcement should reflect the current state of the database within the transaction.

To Reproduce

begin transaction;
create table test (id text primary key);
insert into test values ('Bob');
delete from test where id = 'Bob';
insert into test values ('Bob');
commit;

Environment (please complete the following information):

• OS: Linux/Ubuntu 20.04
• DuckDB Version: 0.2.7

Before submitting

• Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?
• Have you tried this on the latest master branch?

[hawkfish]

Looks like the key is still in the transaction's copy of the index for the second insert.

[Mytherin]

This seems related to #1631.

[hawkfish]

LocalStorage::Delete does not seem to check the index at all. Since the change is in transaction-local storage, how easy is it to fix this without causing problems with the other transitions' view of the index?

[Mytherin]

LocalStorage::Delete does not seem to check the index at all. Since the change is in transaction-local storage, how easy is it to fix this without causing problems with the other transitions' view of the index?

Since the local storage is completely isolated from other transactions this specific set of queries should not be so difficult to solve, as LocalStorage::Delete could simply delete data inside the transaction-local indexes. This would only work if the insert and delete occur in the same transaction, however.

[hawkfish]

@akbertram Can you check whether this fixes your issue?

[akbertram]

@hawkfish Thanks! I have DuckDb building locally but not the JDBC connector apparently. Can you point me to any instructions on how to build the java library?

[hawkfish]

I think all you need to do is this:

• To build optional components, use the flags defined in the Makefile, e.g. to build the JDBC driver, run BUILD_JDBC=1 make.

[akbertram]

This is working perfectly in 0.5.0, thanks!