Re: Hard problem with concurrency

Christopher Kings-Lynne wrote:
> > If you don't have a primary key already, create a unique index on the
> > combination you want to be unique. Then:
> >
> > . Try to insert the record
> > . If you get a duplicate key error
> > then do update instead
> >
> > No possibilities of duplicate records due to race conditions. If two
> people
> > try to insert/update at the same time you'll only get one of the two
> results,
> > but that's the downside of the general approach you've taken. It's a tad
> > inefficient if the usual case is updates, but certainly not less efficient
> > than doing table locks.
>
> The idea was to stop our postgres logs being spammed up with unique
> constraint violation warnings....in which case your solution above is
> identical to our current one. Update and if it fails, insert, except since
> the row is likely to already be there - our current way will be a bit more
> efficient.

To control the spamming, use server_min_messages before the INSERT.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073