Re: 9.5: UPDATE/DELETE .. ORDER BY .. LIMIT ..

On Sun, May 11, 2014 at 10:17 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> On 11 May 2014 11:18, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>>> I don't know. I'd find UPDATE/DELETE ORDER BY something rather
>>> useful.
>
>> Perhaps if an index exists to provide an ordering that makes it clear
>> what this means, then yes.
>
> The $64 question is whether we'd accept an implementation that fails
> if the target table has children (ie, is partitioned). That seems
> to me to not be up to the project's usual quality expectations, but
> maybe if there's enough demand for a partial solution we should do so.
>
> It strikes me that a big part of the problem here is that the current
> support for this case assumes that the children don't all have the
> same rowtype. Which is important if you think of "child table" as
> meaning "subclass in the OO sense". But for ordinary partitioning
> cases it's just useless complexity, and ModifyTable isn't the only
> thing that suffers from that useless complexity.
>
> If we had a notion of "partitioned table" that involved a restriction
> that all the child tables have the exact same rowtype, we could implement
> UPDATE/DELETE in a much saner fashion --- just one plan tree, not one
> per child table --- and it would be possible to support UPDATE/DELETE
> ORDER BY LIMIT with no more work than for the single-table case.
> So that might shift the calculation as to whether we're willing to
> accept a partial implementation.

I think there are many use cases where current inheritance mechanism
is used for partitioning the table without adding new columns in child
table, so if we could support UPDATE/DELETE .. ORDER BY for
those cases, then it will be quite useful, but not sure if it is viable to
see simpler implementation for this case along with keeping current logic.

> Another idea is that the main reason we do things like this is the
> assumption that for UPDATE, ModifyTable receives complete new rows
> that only need to be pushed back into the table (and hence have
> to already match the rowtype of the specific child table). What if
> we got rid of that and had the incoming tuples just have the target
> row identifier (tableoid+TID) and the values for the updated columns?
> ModifyTable then would have to visit the old row (something it must
> do anyway, NB), pull out the values for the not-to-be-updated columns,
> form the final tuple and store it. It could implement this separately
> for each child table, with a different mapping of which columns receive
> the updates.

How about sorting step, are you thinking to have MergeAppend
node for it beneath ModifyTable?

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com