Re: SQL design pattern for a delta trigger? - Mailing list pgsql-general

Vivek Khera wrote:
> On Dec 7, 2007, at 11:42 AM, Colin Wetherbee wrote:
>
>> You can do this with a conditional.  Something like the following
>> should work.
>>
>> IF
>>  NOT (a query matching your data returns rows)
>> THEN
>>  INSERT (your new data)
>
> There exists a race condition here unless you've locked your tables.

Yes, clearly.  In the context of the thread, I was assuming my algorithm
would be implemented as an atomic transaction.

For what it's worth, the real algorithm would be as follows.  I hadn't
had enough coffee yet, and I forgot the UPDATE bit.

IF
   (a query matching your old data returns rows)
THEN
   UPDATE with your new data
ELSE
   INSERT your new data

Colin