At 05:23 AM 9/7/2011, Merlin Moncure wrote:
>On Tue, Sep 6, 2011 at 3:45 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>
> > b) doesn't block reads if you lock in EXCLUSIVE mode. a) is the best
> > way to go if you prefer to handle errors on the client and/or
> > concurrency is important...c) otherwise.
>
>whoops! meant to say b) otherwise! As far as c) goes, that is
>essentially an advisory lock for the purpose -- using advisory locks
>in place of mvcc locks is pretty weak sauce -- they should be used
>when what you are locking doesn't follow mvcc rules.
>
>merlin
Don't you have to block SELECTs so that the SELECTs get serialized?
Otherwise concurrent SELECTs can occur at the same time, find no
existing rows, then "all" the inserts proceed and you get errors (or dupes).
That's how Postgresql still works right? I haven't really been keeping up.
From what I see this (UPSERT/MERGE) has been a common problem/query
over the years but it's not in a Postgresql FAQ and many people seem
to be using methods that don't actually work. Google shows that many
are even recommending those methods to others. Postgresql might still
get blamed for the resulting problems.
Regards,
Link.