Re: duplicate key violate error

AI Rumman wrote:
> I got duplicate key violate error in the db log for the following
query:
> INSERT INTO tab1 ( SELECT '1611576', '1187865' WHERE NOT EXISTS (
SELECT 1 FROM tab1
> WHERE id='1611576' AND id2='1187865' ) )
>
> The error occured during production time.
> But when I manually executed the query, it inserted one row with
success and next time it inserted 0
> rows.
>
> \d tab1
> id int
> id2 int
> primary key (id,id2)
>
> So, any idea why the error occurred at production time.

Concurrency?

Session 1:

CREATE TABLE tab1 (
id integer NOT NULL,
id2 integer NOT NULL,
PRIMARY KEY (id, id2)
);

START TRANSACTION;

INSERT INTO tab1
(SELECT '1611576', '1187865' WHERE NOT EXISTS
(SELECT 1 FROM tab1 WHERE id='1611576' AND id2='1187865')
);

Session 2:

START TRANSACTION;

INSERT INTO tab1
(SELECT '1611576', '1187865' WHERE NOT EXISTS
(SELECT 1 FROM tab1 WHERE id='1611576' AND id2='1187865')
);

Session 1:

COMMIT;

Session 2:

ERROR: duplicate key value violates unique constraint "tab1_pkey"

Yours,
Laurenz Albe