> -----Message d'origine----- > De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : mardi, novembre 27, 2007 23:46 > À : Daniel Caune > Cc : pgsql-sql@postgresql.org > Objet : Re: [SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE > > "Daniel Caune" <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
