On 4 April 2018 at 18:08, Tom Lane <t...@sss.pgh.pa.us> wrote: > Simon Riggs <si...@2ndquadrant.com> writes: >> On 4 April 2018 at 17:19, Tom Lane <t...@sss.pgh.pa.us> wrote: >>> BTW, poking around in the grammar, I notice that MergeStmt did not >>> get added to RuleActionStmt. That seems like a rather serious >>> omission. > >> MERGE isn't a privilege, a trigger action or a policy action. Why >> would it be in RuleActionStmt? > > Because it seems likely that somebody would want to write a rule along > the lines of "ON UPDATE TO mytable DO INSTEAD MERGE ...". > > Looking a little further ahead, one can easily envision somebody > wanting to do "ON MERGE TO mytable DO INSTEAD something". I'd be > prepared to give a pass on that for the present, partly because > it's not very clear what stuff from the original MERGE needs to be > available to the rule. But the other case seems pretty fundamental. > MERGE is not supposed to be a utility command IMO, it's supposed to > be DML, and that means it needs to work anywhere that you could > write e.g. UPDATE.
MERGE is important because it is implemented by other databases, making it an application portability issue and an important feature for PostgreSQL adoption with real users. Enhancing Rules to allow interoperation with MERGE doesn't fall into that same category, so I don't see it needs to work anywhere you can write UPDATE - that certainly isn't the case with triggers, row level security policies or privileges. With that said, we can still discuss it to see if it's possible. ... ON UPDATE TO foo DO INSTEAD MERGE... would look like this MERGE INTO foo USING (what?) ON (join condition) WHEN MATCHED THEN UPDATE which if we managed to achieve that is simply a much poorer version of UPDATE, since MERGE with a single WHEN clause is semantically similar but higher overhead than a single DML operation. So if we implemented it you wouldn't ever use it. Achieving the marriage between rules and merge is made complex in the source and join condition. MERGE is different from other DML in that it always has two tables, so is hard to see how that work with rules on just one table. So that leaves the thought of could we do a more complex version with some kind of exploitation of multi-DML features? Well possibly, but I think its months of work. Adding this requested feature doesn't enhance the original goal of application portability and standards compliance, so there's no way to know when you've got it right. Without a scope or a spec it would be difficult to see where that would go. I would be most glad to be proved wrong to allow us to implement something here and I have no objection to others adding that, though it seems clear that can't happen in this release. For this release, in discussion with Stephen and others at the Dev Meeting in Brussels the addition of features for RLS and partitioning were decided as the priorities for MERGE ahead of other items. This item wasn't mentioned by anyone before and not at all by any users I've spoken to. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services