SELECT FOR UPDATE and LIMIT 1 behave oddly

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: SELECT FOR UPDATE and LIMIT 1 behave oddly
Date: 2004-10-14 01:05:43
Message-ID: 200410131805.43594.josh@agliodbs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Guys,

Summary: SELECT FOR UPDATE and LIMIT behave oddly when combined
Affects: 7.4.3 (not tested yet on other versions)
Severity: Annoyance
Description:
If you attempt to lock a row "off the top" of a table by using SELECT ... FOR
UPDATE LIMIT 1, any blocked transaction will have no rows returned when the
lock ends. This is counter-intuitive and wierd. It is easily worked
around, though, since the LIMIT 1 is really superfluous; possibly we don't
want to fix it, just put a warning in the docs.

Test Case:
primer=# create table some_que ( sequence int, done boolean );
CREATE TABLE
primer=# insert into some_que values ( 1, false );
primer=# insert into some_que values ( 2, false );
primer=# insert into some_que values ( 3, false );
primer=# insert into some_que values ( 4, false );
primer=# insert into some_que values ( 5, false );
primer=# insert into some_que values ( 6, false );

TRANSACTION A:
primer=# begin;
BEGIN
primer=# select * from some_que where done = false order by sequence limit 1
for update;
sequence | done
----------+------
1 | f

TRANSACTION B:
primer=# begin;
BEGIN
primer=# select * from some_que where done = false order by sequence limit 1
for update;

TRANSACTION A:
primer=# update some_que set done = true where sequence = 1;
UPDATE 1
primer=# commit;
COMMIT

TRANSACTION B:
sequence | done
----------+------
(0 rows)

... as you can see, it falsely reports no rows.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2004-10-14 04:02:32 Re: SELECT FOR UPDATE and LIMIT 1 behave oddly
Previous Message Gaetano Mendola 2004-10-13 13:33:59 Re: BUG #1285: Violacion de segmento