Re: atomic multi-threaded upsert

On 25.11.2010 12:51, MIkhail Puzanov wrote:

2010/11/25 Tom Lane <tgl@sss.pgh.pa.us>

"Mikhail V. Puzanov" <misha.puzanov@gmail.com> writes:
>    -- increment and get the counter if exists
>   UPDATE sequences SET counter = counter + 1
>   WHERE section = 'testSection' AND name = 'testKey'
>   RETURNING counter;

Seems that what you have here is a bad manual implementation of a
sequence.  Why don't you use a real sequence object and nextval()?

Well, if I use the real sequence, I'll need unpredictable number of them
and will have to store mapping (section, name)->sequenceName, and
also will have to create those sequences dynamically the similar way.

Finally the problem solved with application level locks (that works for only
one application server, but suits our needs for now).

This also works http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE but we need some more or less cross-DBMS approach.