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
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
