BUG #13496: INSERT WHERE NOT EXISTS error

The following bug has been logged on the website:

Bug reference: 13496
Logged by: Radu Ovidiu
Email address: ilies(dot)ovidiu(at)googlemail(dot)com
PostgreSQL version: 9.4.4
Operating system: Debian Linux 8 / Redhat Linux 6.5
Description:

Hi,

I run a web server with high loads ~ 700 users / minute.

Sometimes (rare) I get an error like duplicate value violation on insert on
field "search_term".

Table is like:
id, primary key
search_term, varchar(255), UNIQUE

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.