Re: INSERT OR UPDATE?
От | Lincoln Yeoh |
---|---|
Тема | Re: INSERT OR UPDATE? |
Дата | |
Msg-id | 5.2.1.1.1.20051010192346.040a73c0@localhost обсуждение исходный текст |
Ответ на | Re: INSERT OR UPDATE? (David Fetter <david@fetter.org>) |
Ответы |
Re: INSERT OR UPDATE?
|
Список | pgsql-general |
Actually I think the uniqueness constraint (due to the primary key) is the one handling it. There's nothing special about that function that will prevent duplicates. Try running it without the primary key specifications in two separate concurrent transactions. Then commit both transactions. Similarly the other methods will be fine as long as there is a uniqueness constraint. If you don't have a uniqueness constraint or you don't want to trigger and exception/error (which could be troublesome in versions of Postgresql without savepoints) then you will have to use locking. It's actually quite surprising how many people get this wrong and don't realize it (I wonder how many problems are because of this). The SQL spec should have had a PUT/MERGE decades ago. The insert vs update format being different is also annoying, oh well. Regards, Link. At 10:01 AM 10/9/2005 -0700, David Fetter wrote: >This is very clever, but it has a race condition. What happens if >between the time of the EXISTS() check and the start of the UPDATE, >something happens to that row? Similarly, what if a row comes into >existence between the EXISTS() check and the INSERT? > >The UPSERT example below, while a little more complicated to write and >use, handles this. > >http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING > >SQL:2003 standard MERGE should fix all this. > >Cheers, >D >-- >David Fetter david@fetter.org http://fetter.org/ >phone: +1 510 893 6100 mobile: +1 415 235 3778 > >Remember to vote! > >---------------------------(end of broadcast)--------------------------- >TIP 5: don't forget to increase your free space map settings
В списке pgsql-general по дате отправления: