select ... for update limit 1

Hi,

What should the official behaviour of
select ... for update limit 1 be?

This is one of the methods I'm considering for multiple worker processes to
each select a _different_ task from the same "todo queue" table.

e.g.
begin;
select taskid from todoqueue where assignedpid=0 for update limit 1;
update todoqueue set assignpid=$mypid where taskid=$taskid;
commit;

Would it be better to lock the table instead? Locking the table would stop
the scheduler from inserting new tasks to the queue table, which is not
desirable.

Right now select for update limit 1 seems to lock all rows in the where
clause, not just retrieved rows. This is ok (would be nicer if it was just
one, but that's pushing it ;) ), but I suspect I'm approaching a less
travelled region with this :). Any potential gotchas I should look out for?

Thanks,
Link.

Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my> writes:
> Right now select for update limit 1 seems to lock all rows in the where
> clause, not just retrieved rows.

This claim surprises me quite a lot, since the FOR UPDATE locking occurs
at the outer level of the executor where rows are about to be returned.
And I can't duplicate your result in a simple test. Could you post a
reproducible example?

regards, tom lane

At 12:51 AM 19-02-2001 -0500, Tom Lane wrote:
>Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my> writes:
>> Right now select for update limit 1 seems to lock all rows in the where
>> clause, not just retrieved rows.
>
>This claim surprises me quite a lot, since the FOR UPDATE locking occurs
>at the outer level of the executor where rows are about to be returned.
>And I can't duplicate your result in a simple test. Could you post a
>reproducible example?

You're right, I'm wrong. Sorry.

It doesn't lock all the rows, but it blocks other similar selects. That's
cool. I may try order by random :). Wondering if there's a way to select
and update at the same time. Maybe I should try a different approach.

**Version:
select version();
version
---------------------------------------------------------------------
PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
(1 row)

**Method
create table sch_todo_q (taskid int, pid int, posted timestamp, status text);
insert into sch_todo_q (taskid,pid,posted,status) values (1,0,now(),'A');
insert into sch_todo_q (taskid,pid,posted,status) values (2,0,now(),'A');
insert into sch_todo_q (taskid,pid,posted,status) values (3,0,now(),'A');
insert into sch_todo_q (taskid,pid,posted,status) values (4,0,now(),'A');
insert into sch_todo_q (taskid,pid,posted,status) values (5,0,now(),'A');

**psql connection 1
begin;
select * from sch_todo_q where pid=0 for update limit 1;
taskid | pid | posted | status

--------+-----+------------------------+--------
1 | 0 | 2001-02-19 14:21:20+08 | A
(1 row)

**psql connection 2
begin;
select * from sch_todo_q where pid=0 for update limit 1;
(blocks)

**psql connection 3
begin;
select * from sch_todo_q where pid=0 order by taskid desc for update limit 1;
taskid | pid | posted | status

--------+-----+------------------------+--------
5 | 0 | 2001-02-19 14:21:20+08 | A
(1 row)

Have fun,
Link.