Re: What is the postgres version of mysql's "ON DUPLICATE KEY"

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Pierre-Frdric Caillaud wrote:
> INSERT INTO related_products (product_id,related_product_id)
> SELECT 1, 2 WHERE NOT EXISTS (SELECT 1
> FROM
> related_products
> WHERE
> product_id = 1 AND related_product_id = 2)
>>
>> Should not the SELECT be FOR UPDATE ?
>> because if no insert is done, the OP wanted to UPDATE the row, so it
>> should not be deleted by another transaction in-between...
>>
>> Can the above query fail if another transaction inserts a row
>> between the SELECT and the INSERT or postgres guarantee that this
>> won't happen ?

> There is no "between" a single statement.

Sure there is. In the above example, the EXISTS result will be correct
as of the time of the snapshot that was taken at the start of the
command (or the start of the whole transaction, if using SERIALIZABLE
mode). So it is *entirely* possible for the INSERT to fail on duplicate
key if some other transaction commits a conflicting row concurrently.

AFAIK, all the bulletproof solutions for this sort of problem involve
being prepared to recover from a failed insertion. There are various
ways you can do that but they all come down to needing to catch the
duplicate key error. In the past you have had to code that in
client-side logic. In 8.0 you could write a plpgsql function that
catches the exception.

Given the need for a test anyway, I think the WHERE NOT EXISTS above
is pretty much a waste of time. Just do an INSERT, and if it fails do
an UPDATE; or do an UPDATE, and if it fails (hits zero rows) then do
an INSERT, being prepared to go back to the UPDATE if the INSERT fails.
Which of these is better probably depends on how often you expect each
path to be taken.

regards, tom lane