Your problem is that the trigger's "found" check will not see the row inserted by a concurrent transaction. In other words, your insert actually fails, the record what you see was inserted by another concurrent transaction, and the "found" check didn't work because the other transaction started after yours, but was quicker, and your transaction can't see it's results. What you try to do is similar to the "insert-or-update" thing, which cannot be done safely in the way you tried to do it. Don't even bother to try, there are lots of discussions on the list and the conclusion is you can't avoid a race condition between the concurrent inserts. There always will be a way one of them will fail with an error. You could actually ignore the error if it's not part of a bigger transaction, which would of course be broken by the error. Your only way to avoid the error completely is to place a save point before the insert, catch the error, and roll back to the save point, and then continue your transaction as you need. HTH, Csaba. On Mon, 2005-06-13 at 18:22, ON.KG wrote: > Hi All! > > I have table: > > CREATE TABLE table1 ( > ip char(15) NOT NULL, > hits integer NOT NULL default '1', > PRIMARY KEY (ip) > ); > > So it's counting hits per each IP for current day and every day > trancated by cron: > TRUNCATE TABLE table1; > > 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 > > But record is inserted into table > > what may be the problem? > > i also tried before: > SELECT INTO cnt hits > FROM table1 > WHERE ip = some_ip; > > IF FOUND THEN > UPDATE table1 > SET hits = hits + 1 > WHERE ip = some_ip; > ELSE > INSERT INTO table1 > (ip) > VALUES > (some_ip); > END IF; > > But same error still appears > > Thank You > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
