"Uwe C. Schroeder" <uwe@oss4u.com> writes:
> I use a stored proc to get the next identifier:
> CREATE OR REPLACE FUNCTION ib_nextval (character varying) RETURNS character
> varying
> ...
> BEGIN
> LOCK TABLE ib_counter IN ACCESS EXCLUSIVE MODE;
> UPDATE ib_counter SET last_value=last_value+1 WHERE name=countername;
> SELECT INTO cprefix,counter,dlen prefix,last_value,display_length FROM ib_counter WHERE name=countername;
> My assumption would be that if I do an exclusive lock on the table I
> can't do the update or a second exclusive lock, so the stored proc
> should block (or fail).
It does block, and it does do the update correctly (at least if you're
not doing this in serializable mode). The problem is that the SELECT
doesn't get the right result. The SELECT actually sees two row versions
as being valid: the one you just created by UPDATE, and whichever one
was current when the outer transaction started. But SELECT INTO will
return at most one row, so it's roll-of-the-dice which one you get.
You can avoid this by attaching FOR UPDATE to the SELECT.
There have been discussions about this effect in the past (try searching
the pghackers archives for mentions of SetQuerySnapshot). In this
particular example it definitely seems like a bug, but if we fix it by
performing SetQuerySnapshot between statements of a plpgsql function,
we may break existing applications that aren't expecting that to happen.
So far there's not been a consensus to change the behavior.
BTW, I'd lose the LOCK if I were you; it doesn't do anything for you
except prevent concurrent updates of different counters. The row lock
obtained by the UPDATE is sufficient.
regards, tom lane