The following bug has been logged online: Bug reference: 5129 Logged by: Thach Anh Tran Email address: myqua...@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). -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs