Re: INSERT OR UPDATE?

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