Re: LIMIT for UPDATE and DELETE

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company