Gregory Stark wrote:
"Florian G. Pflug" <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
