Thread: SQL functions not locking properly?
I'm having a problem with functions written in SQL. Specifically, they
don't seem to be adhering to Postgres locking rules. For the record, I'm
using postgres 7.0.2, installed from RPMs, on Red Hat 6.2.
Here's what I'm seeing:
(psql input represented by '<<'; output represented by '>>'.)
session1<< create table idseq
session1<< (
session1<< name varchar(32) not null,
session1<< id int8 not null default 0
session1<< );
session1>> CREATE
session1<< insert into idseq values ('myid');
session1>> INSERT 20700 1
Each row in the table is supposed to represent a named numeric sequence,
much like the sequences built into postgres. (Mine use an int8 though,
so their values can be much higher.)
session1<< create function nextid( varchar(32)) returns int8 as '
session1<< select * from idseq where name = $1::text for update;
session1<< update idseq set id = id + 1 where name = $1::text;
session1<< select id from idseq where name = $1::text;
session1<< ' language 'sql';
session1>> CREATE
The idea here is that the select...for update within the nextid() function
will establish a row level lock, preventing two concurrent function calls
from overlapping.
Next, I test with two sessions as follows:
session1<< begin;
session1>> BEGIN
session2<< begin;
session2>> BEGIN
session1<< select nextid('myid');
session1>> nextid
session1>> --------
session1>> 1
session1>> (1 row)
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!
session1<< select * from idseq;
session1>> name | id
session1>> ------+----
session1>> myid | 1
session1>> (1 row)
session2<< select * from idseq;
session2>> name | id
session2>> ------+----
session2>> myid | 1
session2>> (1 row)
As you can see, my nextid() function is not synchronized the way I hoped.
I don't know why though. Can someone help?
Thanks,
Forest Wilkinson
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
Forest Wilkinson wrote:
>
> I'm having a problem with functions written in SQL. Specifically, they
> don't seem to be adhering to Postgres locking rules. For the record, I'm
> using postgres 7.0.2, installed from RPMs, on Red Hat 6.2.
>
> Here's what I'm seeing:
> (psql input represented by '<<'; output represented by '>>'.)
>
> session1<< create table idseq
> session1<< (
> session1<< name varchar(32) not null,
> session1<< id int8 not null default 0
> session1<< );
> session1>> CREATE
>
> session1<< insert into idseq values ('myid');
> session1>> INSERT 20700 1
>
> Each row in the table is supposed to represent a named numeric sequence,
> much like the sequences built into postgres. (Mine use an int8 though,
> so their values can be much higher.)
>
> session1<< create function nextid( varchar(32)) returns int8 as '
> session1<< select * from idseq where name = $1::text for update;
> session1<< update idseq set id = id + 1 where name = $1::text;
> session1<< select id from idseq where name = $1::text;
Queries in a function are executed under the
snapshot of the top level query.
So SELECT could never see changes made by
other backends once the top level query
started. If you change the definition of above
function to return setof int8,you may see 2
rows returned.
Maybe you could add 'for update' clause to your
above query.
> session1<< ' language 'sql';
> session1>> CREATE
>
Regards.
Hiroshi Inoue
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