From: Richard Huxton <dev@archonet.com>
To: ON.KG <skyer@on.kg>
Cc: pgsql-general@postgresql.org
Subject: Re: duplicate key violates unique constraint
Date: Mon, 13 Jun 2005 15:43:41 +0100
Message-ID: <42AD9B9D.6080902@archonet.com> (raw)
In-Reply-To: <10813889593.20050613202239@on.kg>
References: <42A95936.3060002@archonet.com>
<20050610095655.0215752956@svr1.postgresql.org>
<10813889593.20050613202239@on.kg>
ON.KG wrote:
>
> before inserting or updating this table there're some checkings,
> logs, etc., so I'm using PL/PgSQL for that
>
> after all checkings and logs I have:
>
> UPDATE table1
> SET hits = hits + 1
> WHERE ip = some_ip;
>
> IF NOT FOUND THEN
> INSERT INTO table1
> (ip)
> VALUES
> (some_ip);
> END IF;
>
> when IP is not found in table it inserts new record into table
> but in logs i see error
> ERROR: duplicate key violates unique constraint "table1"
> CONTEXT: PL/pgSQL function "insert_table1" line 68 at SQL statement
If you can have more than one client running this at once you have a
race condition here. The order runs something like:
1. client A tries to update SOME_IP, no rows affected
2. client B tries to update SOME_IP, no rows affected
3. client A tries the insert of SOME_IP
4. client B tries the insert of SOME_IP - fails!
If you have more than one client, this can always happen. You have two
choices:
1. Use a lock to stop two clients interacting like this
2. Catch the error on the insert and try the update again. This
requires version 8.0 or higher.
--
Richard Huxton
Archonet Ltd
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, skyer@on.kg, pgsql-general@postgresql.org
Subject: Re: duplicate key violates unique constraint
In-Reply-To: <42AD9B9D.6080902@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox