Message-ID: <42AD9B9D.6080902@archonet.com>
Date: Mon, 13 Jun 2005 15:43:41 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317)
MIME-Version: 1.0
To: "ON.KG" <skyer@on.kg>
Cc: pgsql-general@postgresql.org
Subject: Re: duplicate key violates unique constraint
References: <42A95936.3060002@archonet.com>
	<20050610095655.0215752956@svr1.postgresql.org>
	<10813889593.20050613202239@on.kg>
In-Reply-To: <10813889593.20050613202239@on.kg>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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