Re: UPSERT

"Florian G. Pflug" <fgp(at)phlo(dot)org> writes:

>>> INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
>>> This allow to make an insert, and if the key is already there to modify the
>>> value depending on the current one.
>
> May this could be generalized to a generic "<stmt> on <error> do <stmt>"?
> You could then write
> "update table set c=c+1 on not_found do insert into table (a,b,c) values (1,2,3)"
>
> Just an idea I just had...

We have such a thing, subtransactions.

The reason UPSERT or ON DUPLICATE is interesting is because it provides a way
to do it atomically. That is, you keep the locks acquired from the duplicate
key check and if it fails you update the same records you just found violating
the duplicate key.

If the user tries to do the same thing he has to repeat the search after the
duplicate key check has released the locks so it's possible they've been
deleted or updated since. So the user has to loop in case the update fails to
find any records and he has to start over trying to insert. The same problem
plagues you if you do it the other way around too.

The tricky part is avoiding race conditions. The way the unique index code
avoids having someone else come along and insert at the same time is by
holding a lock on an index page. I'm not sure if you can keep that lock while
you go lock the tuples for the update.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com