Re: [HACKERS] MERGE SQL Statement for PG11

2018-01-29 15:40 GMT+01:00 Simon Riggs <simon(at)2ndquadrant(dot)com>:

> On 29 January 2018 at 14:19, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
> >> The concurrency rules are very simple:
> >> If a MATCHED row is concurrently updated/deleted
> >> 1. We run EvalPlanQual
> >> 2. If the updated row is gone EPQ returns NULL slot or EPQ returns a
> >> row with NULL values, then
> >> {
> >> if NOT MATCHED action exists, then raise ERROR
> >> else continue to next row
> >> }
> >> else
> >> re-check all MATCHED AND conditions and execute the first action
> >> whose WHEN Condition evaluates to TRUE
> >>
> >>
> >> This means MERGE will work just fine for "normal" UPDATEs, but it will
> >> often fail (deterministically) in concurrent tests with mixed
> >> insert/deletes or UPDATEs that touch the PK, as requested.
> >
> >
> > can be nice to have part about differences between MERGE and INSERT ON
> > CONFLICT DO
>
> We've agreed not to attempt to make it do anything like INSERT ON
> CONFLICT, so we don't need to discuss that here anymore.
>

My note was not against MERGE or INSERT ON CONFLICT. If I understand to
this topic, I agree so these commands should be implemented separately. But
if we use two commands with some intersection, there can be nice to have
documentation about recommended use cases. Probably it will be very often
question.

Regards

Pavel

>
> MERGE can be semantically equivalent to an UPDATE join or a DELETE
> join, and in those cases, MERGE behaves the same. It handles much more
> complex cases also.
>
> MERGE as submitted here follows all MVCC rules similar to an UPDATE
> join. If it hits a problem with concurent activity it throws
> ERROR: could not serialize access due to concurrent update
> to make sure there is no ambiguity (as described directly above).
>
> As we discussed earlier, removing some of those ERRORs and making it
> do something useful instead may be possible later.
>
> --
> Simon Riggs http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>