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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
