On ons, 2009-11-11 at 19:45 -0500, Tom Lane wrote:
> > try
> > insert
> > catch unique_constraint_violation
> > update
> > end try
>
> > this will end up cluttering the logs with all the constraint violation
> > messages.
>
> Really? It's not supposed to.
There might be a different bug here. This doesn't look right:
CREATE LANGUAGE plpgsql;
CREATE TABLE keytest (a int PRIMARY KEY, b text);
CREATE OR REPLACE FUNCTION insert_or_update(new_a int, new_b text)
RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN INSERT INTO keytest VALUES (new_a, new_b); RETURN 'inserted';
EXCEPTION WHEN integrity_constraint_violation THEN UPDATE keytest SET a = new_a, b = new_b; RETURN 'updated';
END;
$$;
SELECT insert_or_update(1, 'one');
SELECT insert_or_update(1, 'one');
SELECT insert_or_update(2, 'two');
SELECT insert_or_update(2, 'two');
Results in:
insert_or_update
------------------inserted
(1 row)
insert_or_update
------------------updated
(1 row)
insert_or_update
------------------inserted
(1 row)
ERROR: duplicate key value violates unique constraint "keytest_pkey"
CONTEXT: SQL statement "UPDATE keytest SET a = $1 , b = $2 "
PL/pgSQL function "insert_or_update" line 6 at SQL statement
