Re: LIMIT for UPDATE and DELETE

(2014/09/17 1:58), Robert Haas wrote:
> On Tue, Sep 16, 2014 at 11:31 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>> Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>> (2014/08/15 6:18), Rukh Meski wrote:
>>>> Based on the feedback on my previous patch, I've separated only the
>>>> LIMIT part into its own feature. This version plays nicely with
>>>> inheritance. The intended use is splitting up big UPDATEs and DELETEs
>>>> into batches more easily and efficiently.
>>>
>>> IIUC, the patch doesn't support OFFSET with UPDATE/DELETE ... LIMIT. Is
>>> that OK? When we support ORDER BY ... LIMIT/OFFSET, we will also be
>>> allowing for OFFSET with UPDATE/DELETE ... LIMIT. So, ISTM it would be
>>> better for the patch to support OFFSET at this point. No?
>>
>> Without ORDER BY you really would have no idea *which* rows the
>> OFFSET would be skipping. Even more dangerously, you might *think*
>> you do, and get a surprise when you see the results (if, for
>> example, a seqscan starts at a point other than the start of the
>> heap, due to a concurrent seqscan from an unrelated query). It
>> might be better not to provide an illusion of a degree of control
>> you don't have, especially for UPDATE and DELETE operations.
>
> Fair point, but I'd lean toward including it. I think we all agree
> the end goal is ORDER BY .. LIMIT, and there OFFSET certainly has
> meaning. If we don't include it now, we'll just end up adding it
> later. It makes for fewer patches, and fewer changes for users, if we
> do it all at once.

I agree with Robert.

Rukh, what do you think as an author?

Thanks,

Best regards,
Etsuro Fujita