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
)
