Re: conditional insert - Mailing list pgsql-general

On Wed, Sep 7, 2011 at 10:45 AM, Lincoln Yeoh <lyeoh@pop.jaring.my> wrote:
> 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.

yeah -- but you only need to block selects if you are selecting in the
inserting transaction (this is not a full upsert).  if both writers
are doing:
begin;
lock table foo exclusive;
insert into foo select ... where ...;
commit;

is good enough.  btw even if you are doing upsert pattern
(lock...select for update...insert/update), you'd be fine with
straight exclusive locks because the 'for update' lock takes a higher
lock that is blocked by exclusive.  A basic rule of thumb is to try
and not fully block readers unless absolutely necessary...basically
maintenance operations.

> 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.

yeah -- there are two basic ways to do upsert -- a) table lock b) row
lock with loop/retry (either in app or server side via procedure).   I
greatly prefer a) for simplicity's sake unless you are shooting for
maximum possible concurrency.

@andrew s: going SERIALIZABLE doesn't help if you trying to eliminate
cases that would push you into retrying the transaction.

merlin