Re: MERGE Specification

On Fri, Aug 6, 2010 at 3:41 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> On Fri, 2010-08-06 at 10:28 +0300, Heikki Linnakangas wrote:
>
> > > SQL:2011 makes no mention of how MERGE should react to statement level
> > > triggers. MERGE is not a trigger action even. Given considerable
> > > confusion in this area, IMHO we should just say the MERGE does not call
> > > statement triggers at all, of any kind.
> >
> > IMO the UPDATE/DELETE/INSERT actions should fire the respective
> > statement level triggers, but the MERGE itself should not.
>
> When, and how?
>
> If an UPDATE is mentioned 5 times, do we call the trigger 5 times?

My current process for BEFOR / AFTER STATEMENT trigger on MERGE is to fire
the triggers for all action types that appears in the command, unless it is
replaced by a INSTEAD rule. But the triggers for one action type will be
fired only once. That means you will get both UPDATE and INSERT triggers be
activated for only once if you are executing a MERGE command with 5 UPDATEs
and 10 INSERTs.

> What happens if none of the UPDATEs are ever executed?
>
> The triggers (I mean triggers for statement) will be fired anyway even the
UPDATE action matches no tuple. This is not for MERGE only. If you update a
table with the command
UPDATE foo SET ... WHERE false;
It will also fire the STATEMENT triggers of UPDATE type on foo (I think so).

And, even not been asked, I want to say that, in current implementation of
MERGE, the row level triggers are fired by the actions that take the
tuples. If one tuple is caught by an UPDATE action, then the UPDATE row
trigger will be fired on this tuple. If it is handled by INSERT action, then
the INSRET row triggers are on.

Hope you agree with my designs.

> Best explain exactly what you mean.
>
> --
> Simon Riggs www.2ndQuadrant.com <http://www.2ndquadrant.com/>
> PostgreSQL Development, 24x7 Support, Training and Services
>
>