Postgres delete before insert in single transaction

Question

PostgreSQL DB: v 9.4.24

create table my_a_b_data ... // with a_uuid, b_uuid, and c columns

NOTE: the my_a_b_data keeps the references to a and b table. So it keeps the uuids of a and b.

where: the primary key (a_uuid, b_uuid)

there is also an index:

create unique index my_a_b_data_pkey
    on my_a_b_data (a_uuid, b_uuid);

In the Java jdbc-alike code, in the scope one single transaction: (start() -> [code (delete, insert)] ->commit()]) (org.postgresql:postgresql:42.2.5 driver)

delete from my_a_b_data where b_uuid = 'bbb';
insert into my_a_b_data (a_uuid, b_uuid, c) values ('aaa', 'bbb', null);

I found that the insert fails, because the delete is not yet deleted. So it fails because it can not be duplicated.

Q: Is it is some kind of limitation in PostgreSQL that DB can’t do a delete and insert in one transaction because PostgreSQL doesn’t update its indexes until the commit for the delete is executed, therefore the insert will fail since the id or key (whatever we may be using) already exists in the index?

What would be possible solution? Splitting in two transactions?

UPDATE: the order is exactly the same. When I test the sql alone in the SQL console. It works fine. We use JDBI library v 5.29.

there it looks like this:

 @Transaction
 @SqlUpdate("insert into my_a_b_data (...; // similar for the delete
 public abstract void addB() ..

So in the code:

this.begin();
this.deleteByB(b_id);
this.addB(a_id, b_id);
this.commit();

Is that supposed to be `b_uuid` rather than `b` in your `insert` statement? If not, then the behavior is not surprising. If you mean `b_uuid`, then this cannot happen if both statements run in that order *in a single transaction*. — Laurenz Albe, Jan 22 '20 at 16:42
b_uuid. yes. it is the reference/id to a and b tables, it keeps references to a and b in this table. updated. — ses, Jan 22 '20 at 16:44
columns: a_uuid, b_uuid, c: - values: aaa, bbb, null. (i guess fixed already). thx — ses, Jan 22 '20 at 16:47
This is definitely not caused by Postgres. Unless you run those two statements in two different (concurrent) transactions this can't happen. I would guess that your methods are called concurrently and thus you create two different (concurrent) transactions from within your program. You need to call both methods in a single transaction — , Jan 22 '20 at 17:00
That what I thought too. Maybe it's jdbi specific then. Not postgess one. — ses, Jan 22 '20 at 19:53
I don't know JDBI, but you might want to check if it runs methods annotated with `@Transaction` in a separate transaction. Consider posting a [mre]. — Mark Rotteveel, Jan 23 '20 at 06:40

score 1 · Answer 1 · edited Jan 22 '20 at 19:46

I had a similar problem to insert duplicated values and I resolved it by using Insert and Update instead of Delete. I created this process on Python but you might be able to reproduce it:

First, you create a temporary table like the target table where you want to insert values, the difference is that this table is dropped after commit.
```
CREATE TEMP TABLE temp_my_a_b_data 
(LIKE public.my_a_b_data INCLUDING DEFAULTS) 
ON COMMIT DROP;
```
I have created a CSV (I had to merge different data to input) with the values that I want to input/insert on my table and I used the COPY function to insert them to the temp_table (temp_my_a_b_data).

I found this code on this post related to Java and COPY PostgreSQL - \copy command:
```
String query ="COPY tmp from 'E://load.csv' delimiter ','";
```
Use the INSERT INTO but with the ON_CONFLICT clause which you can decide to do an action when the insert cannot be done because of specified constrains, on the case below we do the update:
```
INSERT INTO public.my_a_b_data 
SELECT * 
FROM temp_my_a_b_data 
ON CONFLICT (a_uuid, b_uuid,c) DO UPDATE 
  SET a_uuid = EXCLUDED.a_uuid, 
      b_uuid = EXCLUDED. c = EXCLUDED.c;`
```

Considerations:

I am not sure but you might be able to perform the third step without using the previous steps, temp table or copy from. You can just a loop over the values:

INSERT INTO public.my_a_b_data VALUES(value1, value2, null) 
ON CONFLICT (a_uuid, b_uuid,c) DO UPDATE 
   SET a_uuid = EXCLUDED.a_uuid, 
       b_uuid = EXCLUDED.b_uuid, c = EXCLUDED.c;

Postgres delete before insert in single transaction

1 Answers1