The following bug has been logged online:
Bug reference: 5129
Logged by: Thach Anh Tran
Email address: myquartz@gmail.com
PostgreSQL version: 8.3.8
Operating system: Linux
Description: LIMIT not correct.
Details:
the LIMIT clause is not reply correct number of rows and rows returns.
I have 1 table named 'seat' with 3 fields: id (serial), name (varchar),
is_sold (int).
There are 4 rows in the table:
id,name,is_sold
1,Demo 1,0
2,Demo 2,0
3,Demo 3,0
There are 2 session did same kind:
-- session 1
BEGIN;
SELECT * FROM seat WHERE is_sold = 0
ORDER BY id
LIMIT 1
FOR UPDATE;
--- now session 2 does same
BEGIN;
SELECT * FROM seat WHERE is_sold = 0
ORDER BY id
LIMIT 1
FOR UPDATE;
-- session 2 locked by session 1.
--- session 1: continue
-- id that we found from SELECT is 1
UPDATE seat SET is_sold = 1 WHERE id = 1;
COMMIT;
--- session 2: continue, the lock is released.
--- But the SELECT return 0 rows.
-- The desired result is 1 row with id = 2.
UPDATE seat SET is_sold = 1 WHERE id = <null>;
COMMIT;
The result is ok without LIMIT. But too many locked rows, i need only one
row (and should be one locked row?).
The same problem is apply to version 8.1 (CentOS 5.3).
