From: | Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> |
---|---|
To: | Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>, Robert Haas <robertmhaas(at)gmail(dot)com>, Kevin Grittner <kgrittn(at)ymail(dot)com> |
Cc: | Rukh Meski <rukh(dot)meski(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: LIMIT for UPDATE and DELETE |
Date: | 2014-09-27 07:16:19 |
Message-ID: | 54266443.6070802@vmware.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 09/24/2014 05:22 AM, Etsuro Fujita wrote:
> (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?
I have marked this as "returned with feedback" in the commitfest app.
What I'd like to see happen next is:
Rewrite how UPDATEs work, per Tom's suggestion here:
http://www.postgresql.org/message-id/1598.1399826841@sss.pgh.pa.us. Then
implement ORDER BY ... LIMIT on top of that.
A lot of people would also be willing to settle for just implementing
LIMIT without ORDER BY, as a stopgap measure. But the UPDATE rewrite is
what would make everyone most happy.
- Heikki
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2014-09-27 07:23:33 | Re: Turning off HOT/Cleanup sometimes |
Previous Message | Peter Geoghegan | 2014-09-27 06:36:55 | Re: Stating the significance of Lehman & Yao in the nbtree README |