Re: atomic multi-threaded upsert

On 25.11.2010 12:51, MIkhail Puzanov wrote:
>
>
> 2010/11/25 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>>
>
> "Mikhail V. Puzanov" <misha(dot)puzanov(at)gmail(dot)com
> <mailto:misha(dot)puzanov(at)gmail(dot)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.