Re: SQL functions not locking properly?

Forest Wilkinson <fspam@home.com> writes:
> session2<< select nextid('myid');

> (session2 blocks until session1 completes its transaction)

> session1<< commit;
session1> COMMIT

> (session2 resumes)

session2> nextid
session2> --------
session2> 0
session2> (1 row)

> What gives???  I expected the second call to nextid() to return 2!

Hmm.  If you do the same commands without wrapping them in an SQL
function, they operate as expected.  I'll bet there's some nasty
interaction between the FOR UPDATE support and the way that SQL
functions twiddle the current-command counter.  Sigh, another bug.

You might try it in plpgsql to see if that language has the same
bug (and please report back the results!).  If so, the only workaround
right now is not to use a function, or possibly to code it in C using
the SPI interface.

I'm up to my armpits in subselect-in-FROM right now, but will put this
on my to-do list.  Will look at it in a week or two if no one else has
fixed it before then...
        regards, tom lane