Re: LIMIT for UPDATE and DELETE

(2014/09/09 18:57), Heikki Linnakangas wrote:
> On 09/03/2014 06:39 PM, Robert Haas wrote:
>> 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.

My answers are "yes" but "completely rewritten". So, I think we should
work on 2) first ideally, but 2) seems a large project at least to me.
So, I think it would be reasonable to implement UPDATE/DELETE .. LIMIT
based on Rukh' patch for now and put 2) and the re-implementation of 1)
after 2) on the TODO list.

I don't have enough time to review it for a while, so I'd like to work
on it (and postgres_fdw's "update pushdown" enhancement related to it)
at the next CF (I think I can do it earlier). I must apologize to Rukh
for not having enough time for the patch review.

Thanks,

Best regards,
Etsuro Fujita