Re: SQL functions not locking properly?

Tom Lane wrote:

>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 just happen to have a C function that has a similar problem.
In this case, I am again trying to approximate the behavior of postgres
sequences. The main difference is that I'm using alphanumerics, instead
of 64 bit integers, to represent sequential values. The values are very
much like base 36 numbers.

The table used to store these alphanumeric sequences looks like this:

create table my_uid(
name varchar(32) NOT NULL PRIMARY KEY, -- sequence name
uid varchar(32) NOT NULL default(text '0'), -- current value
min_uid varchar(32) NOT NULL default(text '1'),
max_uid varchar(32) NOT NULL default(text
'zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz'),
cycle bool NOT NULL default('f'));

My next_uid() function is written in C, and has an interface like the
built-in nextval() function. It originally did the following:

(Get the desired sequence name from the function arguments;
e.g. "testuid".)

SELECT \"uid\", \"min_uid\", \"max_uid\", \"cycle\" FROM \"my_uid\" WHERE
\"name\" = 'testuid' FOR UPDATE LIMIT 1;

(Detect & report any errors.)

(Calculate a new sequence value according to the row selected;
e.g. "newval".)

UPDATE \"my_uid\" SET \"uid\" = 'newval' WHERE \"name\" = 'testuid';

(Detect & report any errors.)

(Return the new value.)

However, with just those two queries, next_uid() fails during concurrent
calls on the same uid sequence. Here's a log of what happens:
(<< indicates input; >> indicates output.)

session1 << begin;
session1 >> BEGIN

session2 << begin;
session2 >> BEGIN

session1 << select next_uid('foo');
session1 >> next_uid
session1 >> --------------
session1 >> 1
session1 >> (1 row)

session2 << select next_uid('foo');

(session2 blocks until session1 ends its session)

session1 << commit;
session1 >> COMMIT

(session2 resumes)

session2 >> ERROR: testuid: update query processed 0 rows

That error message is generated by my error trapping within next_uid().
It is reporting that SPI_processed is <= 0, right after executing the
update query I listed above. After committing both transactions,
examination of the rows in the database confirms that the session2 didn't
update any rows.

As you can see, the SELECT...FOR UPDATE followed by UPDATE does not work
properly during concurrent access. However, I can achieve the desired
behavior by executing this query before the other two, within the
next_uid() function:

UPDATE \"my_uid\" SET \"uid\" = \"uid\" WHERE \"name\" = 'testuid';

Once I modified the next_uid() code to execute this new query before doing
the rest of its work, concurrent calls worked as expected. So, that's
what I'm doing in my production code. But it's really quite ugly, and I
shouldn't have to do that! I can't explain why this approach works, short
of saying that row level locking is broken in postgres. Furthermore, I am
not confident that this workaround avoids the problem in all cases.

BTW, I'm using postgres 7.0.2 now, but I discovered this problem (and the
workaround) with 6.5.2 or 6.5.3.

>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

Thanks, Tom. Please let me know when there's a fix.
I can provide more detailed C source code if you need it, but I think the
relevant parts of the code are expressed in this message.

Forest Wilkinson