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;