Re: LIMIT for UPDATE and DELETE

On 09/03/2014 06:39 PM, Robert Haas wrote:
> On Wed, Sep 3, 2014 at 11:02 AM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
>> On 9/3/14 4:46 PM, Robert Haas wrote:
>>> Making it
>>> suck more because you don't think it's as important as your feature
>>> is, in my opinion, not cool.
>>
>> I really can't see how that would make inheritance suck *more*. You can't
>> do UPDATE .. ORDER BY now, and you wouldn't be able to do it after the
>> patch. Yeah, sure, perhaps people using inheritance might feel left out,
>> but surely that would just motivate them to work on improving it.
>
> I think it's entirely reasonable for us to require that all new SQL
> features should be required to work with or without inheritance. If
> we took the opposition position, and said that the only things that
> need to work with inheritance are the ones that existed at the time
> inheritance was introduced, then we'd not need to worry about it not
> only for this feature but for row-level security and SKIP LOCKED and
> GROUPING SETS and, going back a bit further, materialized views and
> security-barrier views and LATERAL and CTEs and on and on. Perhaps
> not all of those require any special handling for inheritance
> hierarchies, but some of them surely did, and if even 10% of the SQL
> features that we have added since table inheritance were allowed to
> opt out of supporting it, we'd have a broken and unusable feature
> today.
>
> Now some people might argue that we have that anyway, but the fact is
> that a lot of people are using inheritance today, even with all its
> flaws, and they wouldn't be if there were a long laundry list of
> limitations that didn't apply to regular tables. We should be looking
> to lift the limitations that currently exist, not add more.

I agree. If we are to support UPDATE .. ORDER BY .. LIMIT, it should
work with inheritance. So the path forward is (using Marko's phrasing
upthread):

1) We put the LIMIT inside ModifyTable like this patch does. This
doesn't prevent us from doing ORDER BY in the future, but helps numerous
people who today have to
2) Someone rewrites how UPDATE works based on Tom's suggestion here:
http://www.postgresql.org/message-id/1598.1399826841@sss.pgh.pa.us,
which allows us to support ORDER BY on all tables (or perhaps maybe not
FDWs, I don't know how those work). The LIMIT functionality in this
patch is unaffected.

What's not clear to me is whether it make sense to do 1) without 2) ? Is
UPDATE .. LIMIT without support for an ORDER BY useful enough? And if we
apply this patch now, how much of it needs to be rewritten after 2) ? If
the answers are "yes" and "not much", then we should review this patch
now, and put 2) on the TODO list. Otherwise 2) should do done first.

Etsuro, Kaigei, please take a look at the patch and try to make a guess
on how much of this still needs to be rewritten if we do 2). If not
much, then please continue to review it, with the aim of getting it into
a committable state.

- Heikki