Re: functions, transactions, key violations

> 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;

It looks like some concurrent statement could come between the UPDATE
and the INSERT that can still cause a unique constraint violation.

> 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:
>

[snip]

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

The comment from the example explains it:

-- not there, so try to insert the key
-- if someone else inserts the same key concurrently,
-- we could get a unique-key failure

The idea of the example is to try to update, and if it doesn't affect
any rows, then insert. Some other concurrent transaction could still
insert something after the UPDATE but before the INSERT, so the unique
constraint violation can still occur.

Regards,
Jeff Davis