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