Re: Add support for printing/reading MergeAction nodes - Mailing list pgsql-hackers

On 4 April 2018 at 18:08, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
>> On 4 April 2018 at 17:19, Tom Lane <tgl@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