atomic multi-threaded upsert - Mailing list pgsql-sql

Hi, All

I'm trying to make a kind of upsert for the following table:

CREATE TABLE sequences  (   section VARCHAR( 50 ) NOT NULL,   name VARCHAR( 50 ) NOT NULL,   counter BIGINT NOT NULL
);

CREATE UNIQUE INDEX IDX_SEQUENCES_SN ON sequences(section, name);


Next, I execute the following two queries for that table:
  -- increment and get the counter if exists UPDATE sequences SET counter = counter + 1 WHERE section = 'testSection'
ANDname = 'testKey' RETURNING counter;
 
  -- insert new counter if not exists INSERT INTO sequences ( section, name, counter ) SELECT 'testSection', 'testKey',
0WHERE NOT EXISTS (     SELECT * FROM sequences     WHERE section = 'testSection' AND name = 'testKey' );
 


It works OK when executed in single thread.

When such pairs of queries are executed in parallel (3-10 threads,
1 JDBC connection for each thread, with autoCommit set),
occasionally I get the following error:

ERROR: duplicate key value violates unique constraint "idx_sequences_sn"

1) That looks weird actually, should the INSERT ... NOT EXISTS (...) be
executed   in atomic fashion? Also, AFAIK, UNIQUE INDEX locks rows before
insert (or not?).   Can it be related to  JDBC?   Or it's the result of MVCC conflict resolution?

2) Are there some patterns for such kind of task?


Thanks.