Re: BUG #13496: INSERT WHERE NOT EXISTS error

On Thu, Jul 09, 2015 at 03:53:04PM +0000, ilies(dot)ovidiu(at)googlemail(dot)com wrote:
> The insert query is like:
> INSERT INTO "website"."search_terms" SELECT 'someID', 'someSearchTerm' WHERE
> NOT EXISTS ( SELECT 1 FROM "website"."search_terms" WHERE (("id" = 'someID')
> OR ("term" = 'someSearchTerm')) LIMIT 1 OFFSET 0 )
> It appears this "upsert" is not safe in high concurency mode ~ 20 users /
> second as I see in Google analytics for that period of time.

Of course it's not safe. Whoever told you it's safe was lying.

There is some time between select check, and the insertion. Hence - race
condition.

More information here:
http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/