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.
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index 5a1e33f..6c203b5
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*************** SELECT merge_db(1, 'dennis');
*** 2561,2567 ****
This coding assumes the <literal>unique_violation</> error is caused by
the <command>INSERT</>, and not by, say, an <command>INSERT</> in a
! trigger function on the table. More safety could be had by using the
features discussed next to check that the trapped error was the one
expected.
</para>
--- 2561,2569 ----
This coding assumes the <literal>unique_violation</> error is caused by
the <command>INSERT</>, and not by, say, an <command>INSERT</> in a
! trigger function on the table. It also assumes that the
! <literal>unique_violation</> error is caused by the primary key and not
! another unique constraint. More safety could be had by using the
features discussed next to check that the trapped error was the one
expected.
</para>
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
