Обсуждение: 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.ovidiu@googlemail.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.

On Thu, Jul 9, 2015 at 11:53 AM, <ilies.ovidiu@googlemail.com> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      13496
> Logged by:          Radu Ovidiu
> Email address:      ilies.ovidiu@googlemail.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" =3D
> 'someID')
> OR ("term" =3D '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.
>

=E2=80=8BYou are correct; but this is not a bug.

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

pgsql-general@postgresql.org

David J.
=E2=80=8B

On Thu, Jul 09, 2015 at 03:53:04PM +0000, ilies.ovidiu@googlemail.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/