Re: UPSERT

Gregory Stark wrote:
> "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.

Yeah, I know - but the syntax above would provide a way to write that "inline"
instead of doing it at the application (or plpgsql) level.

> 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.
I agree - my "generic syntax" seems to be too generic, and doesn't take
locking into account.. :-(

> 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.

Maybe doing the following would work:
start:
do_index_lookup
if (found_row) {
lock_row
if (acquired_lock) {
do_update
return
}
//Row was deleted
}
create_row_on_heap
create_index_entry
if (success)
return
else {
mark_row_as_deleted //or remove row?
goto start
}

It seems like this would work without creating a subtransaction, but
I'm not really sure..

greetings, Florian Pflug