Re: [HACKERS] MERGE SQL Statement for PG11

On Thu, Mar 1, 2018 at 4:33 AM, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> wrote:
> What if the updated tuple fails the join qual with respect to the current
> tuple from the source relation but it now matches some other tuple from the
> source relation? I described this case in one of the earlier emails too. In
> this case, we might end up doing an INSERT (if we decide to execute WHEN NOT
> MATCHED action), even though a MATCH exists. If there is no WHEN NOT MATCHED
> action, the current patch will just skip the updated tuple even though a
> match exists, albeit it's not the current source tuple.

If it does happen, then that will typically be because someone else
concurrently updated a row, changing the primary key attributes, or
some unique index attributes that our MERGE joins on, which I think is
pretty rare. I'm assuming that the user does an idiomatic MERGE, like
every example I can find shows, where the join quals on the target
table are simple equality predicates on the primary key attribute(s).

I think it's fine to simply let the insertion fail with a duplicate
error. Is this any different to a concurrent INSERT that produces that
same outcome?

If the MERGE isn't idiomatic in the way I describe, then the INSERT
may actually succeed, which also seems fine.

> Oracle behaves differently and it actually finds a new matching tuple from
> the source relation and executes the WHEN MATCHED action, using that source
> tuple. But I am seriously doubtful that we want to go down that path and
> whether it's even feasible.

I think that that's just a consequence of Oracle using statement level
rollback to do RC conflict handling. It's the same with an UPDATE ...
FROM, or any other type of UPDATE.

> Our regular UPDATE .. FROM does not do that
> either. Given that, it seems better to just throw an error (even when no NOT
> MATCHED action exists) and explain to the users that MERGE will work as long
> as concurrent updates don't modify the columns used in the join condition.
> Concurrent deletes should be fine and we may actually even invoke WHEN NOT
> MATCHED action in that case.

Again, I have to ask: is such an UPDATE actually meaningfully
different from a concurrent DELETE + INSERT? If so, why is a special
error better than a dup violation, or maybe even having the INSERT
(and whole MERGE statement) succeed?

--
Peter Geoghegan