Re: Functions and transactions

In your second paragraph, I think that you are saying that SELECT FOR
UPDATE only locks one row, even though the select itself may return
many. Am I mis-interpreting you? Also, what do you mean by seizing on
a non-active row?

Your assumption about pkey_id is right, I meant for that to mean partial
key, bad naming on my part ;-).

In my case, the third paragraph applies to this situation, because I can
assume that there will always be an entry in the table that will be
active with that pkey_id (lets call this partialKey_id from now on, to
avoid further confusion).

The alternative you offer is a good idea, I didn't realize that I had
the option to create a unique index on a subset of data within the
table. Unfortunately, it will not work in this situation. I don't have
the option to report failure to the front-end application. I suppose,
i'm looking for a method to only allow one invocation of this function,
per partialKey_id, at a time.

If you have any other alternatives or suggestions, I'm all ears, err
eyes... Anyway, thank you ;-)

Kris

Tom Lane wrote:

>Mmm. This might work as you expect in 8.0, but it surely won't in any
>prior release, because before 8.0 we didn't advance the transaction
>snapshot between statements of a function.
>
>Another issue is that your SELECT FOR UPDATE locks only one of the
>rows having the target pkey_id (I assume that column is misnamed and
>isn't actually a primary key?). If it happened to seize on a non-active
>row then it might not accomplish your goal of blocking until other
>updaters of the same row set commit. That would allow the UPDATE to
>start and set its snapshot, which would mean you lose because it
>wouldn't see the newly inserted row from the other transaction.
>
>Even more to the point, if there aren't yet any committed rows at all of
>the target pkey_id, there is nothing for the SELECT FOR UPDATE to block
>on at all. You could fix the first problem in various ways but I see no
>way around this one. Unless you can guarantee that there will always be
>a suitable row already in existence, I think you have to abandon the
>idea of using a SELECT FOR UPDATE for locking.
>
>One possibility is to create a unique partial index:
>
>CREATE UNIQUE INDEX foo ON table1(pkey_id) WHERE active ;
>
>This will provide an enforcement that you don't have more than one
>active row at a time. Now you just simplify the trigger to
> update table1 set active = false where NEW.pkey_id = pkey_id and active;
> NEW.active := true;
>Race conditions will end up causing unique-key errors, which you can just
>retry.
>
> regards, tom lane
>
>