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.
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 |