Re: conditional insert

On Tue, Sep 6, 2011 at 3:45 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Tue, Sep 6, 2011 at 1:50 PM, Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my> wrote:
>> At 07:02 PM 9/5/2011, J. Hondius wrote:
>>>
>>> I agree that there are better ways to do this.
>>> But for me this works. (legacy driven situation)
>>>
>>> INSERT INTO tbinitialisatie (col1, col2)
>>>  SELECT 'x', 'y'
>>>  FROM tbinitialisatie
>>>  WHERE not exists (select * from tbinitialisatie where col1 = 'x' and col2
>>> = 'y')
>>>  LIMIT 1
>>
>> Hi,
>>
>> That does not work 100%. Try it with two psql instances.
>>
>> Do:
>> *** psql #1
>> begin;
>> INSERT INTO tbinitialisatie (col1, col2)
>>  SELECT 'x', 'y'
>>  FROM tbinitialisatie
>>  WHERE not exists (select * from tbinitialisatie where col1 = 'x' and col2 =
>> 'y')
>>  LIMIT 1 ;
>>
>> *** psql #2
>> begin;
>> INSERT INTO tbinitialisatie (col1, col2)
>>  SELECT 'x', 'y'
>>  FROM tbinitialisatie
>>  WHERE not exists (select * from tbinitialisatie where col1 = 'x' and col2 =
>> 'y')
>>  LIMIT 1 ;
>> commit;
>>
>> *** psql #1
>> commit;
>>
>> You should find duplicate inserts.
>>
>> In most cases the "begin" and "commit" are very close together so you won't
>> notice the problem. But one day you might get unlucky.
>>
>> Your options are:
>> a) put a unique constraint and handle the insert errors when they occur
>> b) lock the entire table first (affects performance: blocks all selects on
>> that table)
>> c) use a lock elsewhere (but this requires all applications using the
>> database to cooperate and use the lock).
>> d) wait for SQL MERGE to be implemented ( but from what I see the current
>> proposal seems to require a) or b) anyway:
>> http://wiki.postgresql.org/wiki/SQL_MERGE )
>
> 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