Re: Replaceing records

Csaba Nagy <nagy(at)ecircle-ag(dot)com> writes:
> Thinking about it, there's probably no easy way to avoid race conditions
> (in a true transactional DB at least) when inserting into a table with
> unique constraints. The REPLACE syntax will definitely not do it,
> because I can't imagine what it should do when 2 threads try to REPLACE
> the same key in concurrent transactions. Both will see the key as
> missing, and try to insert it, so back we are at the same problem INSERT
> has.

Assuming that you've got a unique constraint defined, one thread will
succeed in doing the INSERT, and the other will fail with a duplicate
key error --- whereupon it should loop back and try the REPLACE part
again. So what this all comes down to is having control over recovery
from a dup-key error. You have to be able to not have that abort your
whole transaction.

regards, tom lane