Re: duplicate key violates unique constraint

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