Simon Riggs <si...@2ndquadrant.com> writes: > On 11 May 2014 11:18, Andres Freund <and...@2ndquadrant.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. 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. This eliminates the whole multiple-plan-tree business at a stroke ... and TBH, it's not immediately obvious that this would not be as efficient or more so than the way we do UPDATEs today, even in the single-target-table case. Pumping all those not-updated column values through the plan tree isn't free. The more I think about it, the more promising this sounds --- though I confess to being badly undercaffeinated at the moment, so maybe there's some fatal problem I'm missing. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers