Re: BUG #13496: INSERT WHERE NOT EXISTS error

On Thu, Jul 9, 2015 at 11:53 AM, <ilies(dot)ovidiu(at)googlemail(dot)com> wrote:

> 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.
>

​You are correct; but this is not a bug.

If you have an actual question you should post in at:

pgsql-general(at)postgresql(dot)org

David J.
​