Re: functions, transactions, key violations

On Jun 4, 2008, at 5:39 PM, Tom Lane wrote:

> I think you've anonymized the example into nonsense :-(.

Now that I've provided yet another example of the perils of not
providing the exact code, I've tried to patch it (below, for those of
you willing to give me a second chance).

However, my point regarding the example in the docs still holds. Why
is the exception block necessary? Doesn't wrapping the statements in a
function ensure the unique_violation couldn't occur?
Again, this is from
<http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
>

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
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;
Michael Glaesemann
grzm seespotcode net

CREATE TABLE purchases (
purchase_id SERIAL PRIMARY KEY,
item_id INT NOT NULL,
purchased_by int NOT NULL,
purchase_price INT NOT NULL,
purchased_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT
CURRENT_TIMESTAMP,
purchase_status INT NOT NULL DEFAULT 1
);

CREATE UNIQUE INDEX
purchases_purchase_id_where_purchase_status_eq_1_key on purchases
(item_id) WHERE purchase_status = 1;

CREATE OR REPLACE FUNCTION
purchase(IN in_item_id integer,
IN in_purchased_by bigint,
IN in_purchase_price integer)
RETURNS VOID AS
$BODY$
BEGIN
-- some selects
UPDATE purchases
SET purchase_status = 0
WHERE item_id = in_item_id
AND purchase_status = 1;
INSERT INTO purchases (item_id, purchased_by, purchase_price)
VALUES (in_item_id, in_purchased_by, in_purchase_price);
-- some more manipulation
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;