[HACKERS] Re: offset and limit in update and subselect
At 05:07 PM 2/24/01 -0500, Tom Lane wrote: is not a defined concept according to SQL. Even if we allowed queries such as you've described, the results would not be well-defined, but would change at the slightest provocation. The implementation feels itself entitled to rearrange tuple order whenever the whim strikes it. As the documentation tries hard to make plain, LIMIT/OFFSET are only guaranteed to produce reproducible results if there's also an ORDER BY that constrains the tuples into a unique ordering. Hi, Would it then be fine to use update ... limit in the following scenario? I have a todo queue: create table todo ( task text, pid int default 0); The tasks are inserted into the todo table. Then the various worker processes do the following update to grab tasks without duplication. update todo set pid=$mypid where pid=0 limit 1; For me it doesn't matter what which row each worker gets, as long as they only get one each and they are not the same. What would the performance impact of "order by" be in a LIMIT X case? Would it require a full table scan? Thanks, Link.
Re: [HACKERS] Re: offset and limit in update and subselect
Lincoln Yeoh [EMAIL PROTECTED] writes: Would it then be fine to use update ... limit in the following scenario? I have a todo queue: create table todo ( task text, pid int default 0); The tasks are inserted into the todo table. Then the various worker processes do the following update to grab tasks without duplication. update todo set pid=$mypid where pid=0 limit 1; There's no LIMIT clause in UPDATE. You could do something like BEGIN SELECT taskid FROM todo WHERE pid = 0 FOR UPDATE LIMIT 1; UPDATE todo SET pid = $mypid WHERE taskid = $selectedid; COMMIT (assuming taskid is unique; you could use the OID if you have no application-defined ID). What would the performance impact of "order by" be in a LIMIT X case? Would it require a full table scan? Yes, unless there's an index on the order-by item. The above example should be fairly efficient if both pid and taskid are indexed. Hmm ... trying this out just now, I realize that 7.1 effectively does the LIMIT before the FOR UPDATE, which is not the way 7.0 behaved. Ugh. Too late to fix it for 7.1, but I guess FOR UPDATE marking ought to become a plan node just like LIMIT did. regards, tom lane
[HACKERS] Re: offset and limit in update and subselect
At 11:16 PM 25-02-2001 -0500, Tom Lane wrote: Right. Only the first row is locked, but that doesn't help any. "order by random" sounds like it might be a good answer, if there aren't many rows that need to be sorted. Yep. I'll just see what happens in the testing stages. What would happen if I rewrite that query to: update todo set pid = $mypid where exists ( select task id from todo where pid = 0 for update limit 1); Right now you get ERROR: SELECT FOR UPDATE is not allowed in subselects This is something that could be fixed if FOR UPDATE were a plan node instead of a function done at the executor top level. OK. Sounds like it won't be worth the trouble to do, plus deadlocks would be real fun ;). Cheerio, Link.
