Re: LIMIT for UPDATE and DELETE

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.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company