Re: Strang behaviour SELECT ... LIMIT n FOR UPDATE

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

> -----Message d'origine-----
> De : Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Envoyé : mardi, novembre 27, 2007 23:46
> À : Daniel Caune
> Cc : pgsql-sql(at)postgresql(dot)org
> Objet : Re: [SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE
> 
> "Daniel Caune" <daniel(dot)caune(at)ubisoft(dot)com> writes:
> > 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.
> 
> There's no known issue specifically of that form (and a quick test of
> 8.1 doesn't reproduce any such behavior).  However, it is known and
> documented that LIMIT and FOR UPDATE behave rather oddly together:
> the LIMIT is applied first, which means that if FOR UPDATE rejects
> any rows as being no longer up-to-date, you get fewer than the expected
> number of rows out.  You did not mention any concurrent activity in
> your example, but I'm betting there was some ...
> 
> 			regards, tom lane

Yes, you were betting right.  However I would have thought that the SELECT ... FOR UPDATE statement blocks if another process were locking the same rows.

The record values don't change from a call to another.  I did read the documentation, especially the section that Bruce Momjian's pointed me out, but I don't think that it corresponds to this case (cf. my test).

I did the following test, removing all the where-clause from the SELECT statement.  Every statement completes immediately, i.e. it doesn't block.

agoratokens=> select id from "Tokens"
id
-----
  47
 104
  44
  42
  33
  69
  94
  89
  90
 ...

Time: 119.314 ms

agoratokens=> select id from "Tokens" limit 2 for update;
 id
-----
  47
 104
(2 rows)

Time: 17.679 ms


agoratokens=> select id from "Tokens" limit 3 for update;
 id
-----
  47
 104
(2 rows)

Time: 20.452 ms

The statement doesn't return the row where id equals to 44.


agoratokens=> select id from "Tokens" limit 3;
 id
-----
  47
 104
  44
(3 rows)

Time: 1.186 ms

The statement returns the row where id equals to 44.


agoratokens=> select id from "Tokens" limit 3 for update;
 id
-----
  47
 104
(2 rows)

Time: 9.473 ms

The statement still doesn't return the row where id equals to 44.


agoratokens=> select id from "Tokens" where id IN (47, 104, 44, 42) limit 3 for update;
 id
-----
  47
 104
  44
(3 rows)

This time, the statement returns the row where id equals to 44.


agoratokens=> select id from "Tokens" limit 3;
 id
-----
  47
 104
  44
(3 rows)

Time: 7.547 ms


agoratokens=> select id from "Tokens" limit 5 for update;
 id
-----
  47
 104
  33
(3 rows)

Time: 11.725 ms

This time, the statement doesn't return the rows where id equals to 44 and 42.


agoratokens=> select id from "Tokens" limit 8 for update;
 id
-----
  47
 104
  33
  69
  94
  89
(6 rows)

Time: 11.794 ms

The statement still doesn't return the rows where id equals to 44 a 42.


agoratokens=> select id from "Tokens" where id = 44 limit 3 for update;
 id
----
 44
(1 row)

Time: 14.172 ms

The statement does return the row where id equals to 44.


"However, it is known and documented that LIMIT and FOR UPDATE behave rather oddly together: the LIMIT is applied first, which means that if FOR UPDATE rejects any rows as being no longer up-to-date, you get fewer than the expected number of rows out."

Tom, when you say "rows as being no longer up-to-date", do you mean which values don't match anymore the where-clauses of the SELECT statement?  If so, that doesn't correspond to my test since I remove every where-clause.

Any ideas, any other tests I can try?

Thanks,

--
Daniel