Re: Functions and transactions

Kris Kiger <kris(at)musicrebellion(dot)com> writes:
> Hmm.. I was trying simplify my function to get the point across with
> minimal confusion. If you don't think there is enough detail, let me
> know what is lacking and I will add the appropriate detail. The
> function is executed BEFORE insert on table1.

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