Skip site navigation (1) Skip section navigation (2)

Re: UPSERT

From: "Florian G(dot) Pflug" <fgp(at)phlo(dot)org>
To: Gregory Stark <stark(at)enterprisedb(dot)com>
Cc: Andrew Dunstan <andrew(at)dunslane(dot)net>, Jonathan Scher <js(at)oxado(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: UPSERT
Date: 2007-03-02 16:02:28
Message-ID: 45E84A94.6040302@phlo.org (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

  • Re: UPSERT at 2007-03-02 15:06:52 from Gregory Stark

pgsql-hackers by date

Next:From: Gregory StarkDate: 2007-03-02 16:23:20
Subject: GIST and TOAST
Previous:From: Heikki LinnakangasDate: 2007-03-02 15:41:56
Subject: Re: UPSERT

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group