From: Mikhail V. Puzanov <misha.puzanov@gmail.com>
To: pgsql-sql@postgresql.org
Subject: atomic multi-threaded upsert
Date: Thu, 25 Nov 2010 02:16:59 +0600
Message-ID: <4CED72BB.3070905@gmail.com> (raw)
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' AND name = 'testKey'
RETURNING counter;
-- insert new counter if not exists
INSERT INTO sequences ( section, name, counter )
SELECT 'testSection', 'testKey', 0
WHERE 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: misha.puzanov@gmail.com
Subject: Re: atomic multi-threaded upsert
In-Reply-To: <4CED72BB.3070905@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox