Re: BUG #5129: LIMIT not correct.

The following bug has been logged online:

Bug reference: 5129
Logged by: Thach Anh Tran
Email address: myquartz(at)gmail(dot)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(at)gmail(dot)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(at)anarazel(dot)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