Re: LIMIT for UPDATE and DELETE

On 8/29/14 4:33 PM, Tom Lane wrote:
>> So either it has to be inside
>> ModifyTable or the ModifyTable has to somehow pass something to a Limit
>> node on top of it
>
> ... or we add a LockRows node below the Limit node.  Yeah, that would make
> UPDATE/LIMIT a tad slower, but I think that might be preferable to what
> you're proposing anyway.  Raw speed of what is fundamentally a fringe
> feature ought not trump every other concern.

I don't consider this a fringe feature, but in any case, the main use 
case for LIMIT without ORDER BY in UPDATE and DELETE is to split up 
large transactions into smaller batches.  And considering that, I think 
raw speed should be a concern (though it shouldn't trump every other 
concern, obviously).

More to the point, personally, I think the changes to nodeModifyTable.c 
are very reasonable so it's not clear to me that the "extra 
LockRows+Limit nodes" approach would be inherently better (even ignoring 
performance concerns).

>> This is just my personal opinion, but what I think should happen is:
>
>>     2) We allow ORDER BY on tables with no inheritance children using
>> something similar to Rukh's previous patch.
>>     3) Someone rewrites how UPDATE works based on Tom's suggestion here:
>> http://www.postgresql.org/message-id/1598.1399826841@sss.pgh.pa.us,
>
> I still think we should skip #2 and go directly to work on #3.  Getting
> rid of the unholy mess that is inheritance_planner would be a very nice
> thing.

Ideally?  Yeah, that would be great.  But I don't see anyone 
volunteering to do that work, and I think holding back a useful feature 
(ORDER BY with UPDATE/DELETE) in hopes of getting someone to volunteer 
to do it is insane.  Now, you're free to argue that ORDER BY with 
UPDATE/DELETE isn't that useful, of course, but I'm sure there are lots 
of people who agree with me.


.marko