functions, transactions, key violations

We have a function which has been throwing a key violation. We're
tracking the purchase history for a limited number of unique items,
and flagging the most recent purchase with purchase_status = 1.

Stripped down, here's the schema and the function:

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
(purchase_id) WHERE purchase_status = 1;

So, when a purchase is made for a given item, we also need to update
the purchase_status of the previous purchase.

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;

We're getting errors that the INSERT INTO purchases is within the
purchase function is violating the
purchases_purchase_id_where_purchase_status_eq_1_key constraint. How
can this be, if the function, called as a single statement (i.e., not
within an explicit transaction) is its own transaction?

The Postgres documentation shows another example, which leads me to
believe I'm missing something. If a function does occur within a
transaction, I don't understand why the exception block is necessary
in Example 38-1. Exceptions with UPDATE/INSERT [1]. Given the table
from the example:

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

If I issue the following transaction,

BEGIN;
UPDATE db SET b = data WHERE a = key;
INSERT INTO db(a,b) VALUES (key, data);
COMMIT;

and the UPDATE didn't affect any rows, I'd expect the transaction to
be successful.

What am I missing?

Thanks for your help.

Michael Glaesemann
grzm seespotcode net


[1](http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
)