http://www.postgresql.org/docs/devel/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
This example can lead to an infinite loop if there is another column
that has a unique key constraint on it in addition to the primary key
and someone tries to execute the function with a unique primary key
but a duplicate value for the column with the unique constraint.
CREATE TABLE db (a INT PRIMARY KEY, b TEXT UNIQUE);
CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
LOOP
-- first try to update the key
UPDATE db SET b = data WHERE a = key;
IF found THEN
RETURN;
END IF;
-- not there, so try to insert the key
-- if someone else inserts the same key concurrently,
-- we could get a unique-key failure
BEGIN
INSERT INTO db(a,b) VALUES (key, data);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- Do nothing, and loop to try the UPDATE again.
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;
SELECT merge_db(1, 'david');
SELECT merge_db(2, 'david');
The update effects no rows because the primary key value doesn't exist
and the insert fails because the unique key constraint fails but the
exception handling ignores the error. It almost seems like there
should be a primary_key_violation exception type to distinguish, but
all I am suggesting right now is that we make a note of that case in
the docs so that fewer people get stung by this. I have attached a
patch with some suggested wording.
