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