Thread: offset and limit in update and subselect
Hi, It appears that limit and offset do not work in a subselect such as: update my_table set my_col = new_val where oid = (select oid from my_table offset row_number limit 1); Basically, I need to update rows by offset from the beginning of the table. Even nicer would be update my_table set my_col = new_val offset row_number limit 1; But this is not supported either. Tim -- Timothy H. Keitt Department of Ecology and Evolution State University of New York at Stony Brook Phone: 631-632-1101, FAX: 631-632-7626 http://life.bio.sunysb.edu/ee/keitt/
I see this (subselect) is available in >=7.1. Tim Timothy H. Keitt wrote: > Hi, > > It appears that limit and offset do not work in a subselect such as: > > update my_table set my_col = new_val where oid = (select oid from > my_table offset row_number limit 1); > > Basically, I need to update rows by offset from the beginning of the > table. Even nicer would be > > update my_table set my_col = new_val offset row_number limit 1; > > But this is not supported either. > > Tim -- Timothy H. Keitt Department of Ecology and Evolution State University of New York at Stony Brook Phone: 631-632-1101, FAX: 631-632-7626 http://life.bio.sunysb.edu/ee/keitt/
"Timothy H. Keitt" <Timothy.Keitt@SUNYSB.Edu> writes:
> Basically, I need to update rows by offset from the beginning of the
> table.
I think you'd better rethink your data design. Tuple order in a table
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.
regards, tom lane
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.
Lincoln Yeoh <lyeoh@pop.jaring.my> 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
BEGINSELECT 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
At 04:58 PM 25-02-2001 -0500, Tom Lane wrote: > >There's no LIMIT clause in UPDATE. You could do something like Oh. I thought 7.1 had that. > BEGIN > SELECT taskid FROM todo WHERE pid = 0 FOR UPDATE LIMIT 1; > UPDATE todo SET pid = $mypid WHERE taskid = $selectedid; > COMMIT This is very similar to what I'm testing out in 7.0.3 - except I'm currently trying "order by random" to prevent blocking. This is because all worker processes will tend to select stuff in the same order (in the absence of inserts or updates on that table), and thus they will hit the same first row (this is what I encountered last week - and I got the wrong impression that all rows were locked). 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); This is pushing it, but I'm curious on what would happen :). I'll stick to doing it in two queries, and leave out the "order by random"- faster select vs low blocking. Cheerio, Link.
Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
>> BEGIN
>> SELECT taskid FROM todo WHERE pid = 0 FOR UPDATE LIMIT 1;
>> UPDATE todo SET pid = $mypid WHERE taskid = $selectedid;
>> COMMIT
> This is very similar to what I'm testing out in 7.0.3 - except I'm
> currently trying "order by random" to prevent blocking. This is because
> all worker processes will tend to select stuff in the same order (in the
> absence of inserts or updates on that table), and thus they will hit the
> same first row (this is what I encountered last week - and I got the wrong
> impression that all rows were locked).
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.
> 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.
regards, tom lane
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.
Hmmm... that's good to know. Basically, I'm trying to model fixed order tables in another application through a proxy mechanism (see http://rpgsql.sourceforge.net/). I guess I will have to force row ordering on all proxied tables. Tim Tom Lane wrote: > "Timothy H. Keitt" <Timothy.Keitt@SUNYSB.Edu> writes: > >> Basically, I need to update rows by offset from the beginning of the >> table. > > > I think you'd better rethink your data design. Tuple order in a table > 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. > > regards, tom lane -- Timothy H. Keitt Department of Ecology and Evolution State University of New York at Stony Brook Phone: 631-632-1101, FAX: 631-632-7626 http://life.bio.sunysb.edu/ee/keitt/