Re: conditional insert

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.

merlin