Обсуждение: BUG #5129: LIMIT not correct.
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).
On Wednesday 21 October 2009 05:37:36 Thach Anh Tran wrote: > 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. Read the second caution block in the relevant section of the manual: http://www.postgresql.org/docs/current/interactive/sql-select.html#SQL-FOR- UPDATE-SHARE "It is possible for a SELECT command using both LIMIT and FOR UPDATE/SHARE clauses to return fewer rows than specified by LIMIT. This is because LIMIT is applied first. The command selects the specified number of rows, but might then block trying to obtain a lock on one or more of them. Once the SELECT unblocks, the row might have been deleted or updated so that it does not meet the query WHERE condition anymore, in which case it will not be returned. " Andres
Andres Freund <andres@anarazel.de> writes: > On Wednesday 21 October 2009 05:37:36 Thach Anh Tran wrote: >> the LIMIT clause is not reply correct number of rows and rows returns. > Read the second caution block in the relevant section of the manual: > http://www.postgresql.org/docs/current/interactive/sql-select.html#SQL-FOR- > UPDATE-SHARE There has been some discussion of changing that, and it might happen for 8.5. It's essentially impossible to fix in existing releases though, because changing it requires a major restructuring of the executor. regards, tom lane