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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
