Strang behaviour SELECT ... LIMIT n FOR UPDATE

["Daniel Caune" <daniel(dot)caune(at)ubisoft(dot)com>]

Hi,

I'm facing a strange behaviour with a statement SELECT ... LIMIT n FOR
UPDATE in PostgreSQL 8.1.  The number of rows returned is actually (n -
1).  I'm trying to find whether this is an identified issue with
PostgreSQL 8.1 that might have been fixed in a later version such as
8.2; I don't have any problem in moving to a later version if needed.

agoratokens=>  SELECT * FROM "Tokens" INNER JOIN "Tokentypes" ON
"Tokens"."type"="Tokentypes"."type" WHERE "Tokentypes"."tokenName"
='clanName' AND "Tokens"."isLocked" = false limit 2 FOR UPDATE;

 id  | type |          value           | isLocked |         timestamp
| type | tokenName

-----+------+--------------------------+----------+---------------------
-------+------+-----------

 104 |    2 | RegressionTestClanName13 | f        | 2007-11-27
20:40:25.208074 |    2 | clanName

(1 row)

 
agoratokens=>  SELECT * FROM "Tokens" INNER JOIN "Tokentypes" ON
"Tokens"."type"="Tokentypes"."type" WHERE "Tokentypes"."tokenName"
='clanName' AND "Tokens"."isLocked" = false limit 3 FOR UPDATE;

 id  | type |          value           | isLocked |         timestamp
| type | tokenName

-----+------+--------------------------+----------+---------------------
-------+------+-----------

 104 |    2 | RegressionTestClanName13 | f        | 2007-11-27
20:40:25.208074 |    2 | clanName

 118 |    2 | RegressionTestClanName28 | f        | 2007-11-21
21:10:29.872352 |    2 | clanName

(2 rows)


If I remove the FOR UPDATE clause, the SELECT ... LIMIT n statement
returns n rows as expected:

agoratokens=>  SELECT * FROM "Tokens" INNER JOIN "Tokentypes" ON
"Tokens"."type"="Tokentypes"."type" WHERE "Tokentypes"."tokenName"
='clanName' AND "Tokens"."isLocked" = false limit 3;

 id  | type |          value           | isLocked |         timestamp
| type | tokenName

-----+------+--------------------------+----------+---------------------
-------+------+-----------

 104 |    2 | RegressionTestClanName13 | f        | 2007-11-27
20:40:25.208074 |    2 | clanName

  40 |    2 | RegressionTestClanName9  | f        | 2007-10-15
11:27:31.897    |    2 | clanName

 118 |    2 | RegressionTestClanName28 | f        | 2007-11-21
21:10:29.872352 |    2 | clanName

(3 rows)



--
Daniel