Re: select ... for update limit 1

At 12:51 AM 19-02-2001 -0500, Tom Lane wrote:
>Lincoln Yeoh <lyeoh@pop.jaring.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.