CODEWITHSUNDEEP
postgresql
Nick
Nick

Reputation: 565

DELETE then INSERT is randomly raising a duplicate key violates unique constraint error

I have a query (for a website) that replaces old data with new data.

I run the query in one call to the database via the PHP pg_query function and also use pgbouncer with transaction pool mode. I would be very surprised if two of the same queries are running at the same time, but is that the only explanation for this? I don't have any triggers or SERIAL columns on the table.

CREATE TABLE mydata (
    id INT NOT NULL,
    val TEXT NOT NULL
);
ALTER TABLE mydata ADD CONSTRAINT mydata_unique (id);

The statement that raises the conflict is

DELETE FROM mydata WHERE id IN (1,2,3); 
INSERT INTO mydata (id,val) VALUES (1,'one'); 
INSERT INTO mydata (id,val) VALUES (2,'two'); 
INSERT INTO mydata (id,val) VALUES (3,'three');

Version PostgreSQL 12.2

Upvotes: 0

Views: 959

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246838

I assume that you are not running these statements in parallel, but one after the other.

Still, this could easily cause conflicts if several database sessions are doing the same thing at the same time: a second session may insert rows after the first session deleted the old rows, but before it inserted the new rows.

To protect yourself from that with row locks, run all statements in a single transaction. This may occasionally lead to a deadlock, which is no big deal - just repeat the transaction that failed.

Upvotes: 2

Related Questions