On 2010-11-24, "Mikhail V. Puzanov" <misha.puza...@gmail.com> wrote: > 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' > );
> 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? Perhaps - Thread A UPDATEs, affecting no row. Thread B UPDATEs, affecting no row. Thread A INSERTs one row. Autocommit is on, so it commits the INSERT. Thread B attempts INSERT and fails on the duplicate. -- KM -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
