Apart from concurrency issues, it is possible that you
have sequence generation problems. Depending on how
you inserted the original rows into the 'purchases' table, it is possible
that the nextval number has not kept-up and is lagging behind.
You need to ensure that 'purchases_purchase_id_se
On Wed, 2008-06-04 at 18:21 -0400, Michael Glaesemann wrote:
> Yes, I saw the comment. I'm guessing I'm missing something wrt
> transaction isolation level or locking. Would I need to use
> SERIALIZABLE or some kind of locking? Is the function in the example
> any different than the following
Michael Glaesemann <[EMAIL PROTECTED]> writes:
> 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?
Well, the point of that example is to deal corre
On Jun 4, 2008, at 6:00 PM, Jeff Davis wrote:
Some other concurrent transaction could still
insert something after the UPDATE but before the INSERT, so the unique
constraint violation can still occur.
Yes, I saw the comment. I'm guessing I'm missing something wrt
transaction isolation leve
> 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
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
Michael Glaesemann <[EMAIL PROTECTED]> writes:
> 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.
Um, the function is ensuring that there is
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 SERI
