From: | "Thach Anh Tran" <myquartz(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #5129: LIMIT not correct. |
Date: | 2009-10-21 03:37:36 |
Message-ID: | 200910210337.n9L3baZI039042@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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).
From | Date | Subject | |
---|---|---|---|
Next Message | taktos | 2009-10-21 06:48:40 | Re: BUG #5127: AbstractJdbc2Connection#doRollback should throws Exception if connection is closed |
Previous Message | Jesse Morris | 2009-10-20 21:25:18 | Re: Re: BUG #5065: pg_ctl start fails as administrator, with "could not locate matching postgres executable" |