Re: [HACKERS] MERGE SQL Statement for PG11

Hi

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

> On 26 January 2018 at 11:25, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > On 24 January 2018 at 04:12, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> >> On 24 January 2018 at 01:35, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> >>>
> >> Please rebase, and post a new version.
> >>
> >> Will do, though I'm sure that's only minor since we rebased only a few
> days ago.
> >
> > New v12 with various minor corrections and rebased.
> >
> > Main new aspect here is greatly expanded isolation tests. Please read
> > and suggest new tests.
> >
> > We've used those to uncover a few unhandled cases in the concurrency
> > of very comple MERGE statements, so we will repost again on Mon/Tues
> > with a new version covering all the new tests and any comments made
> > here. Nothing to worry about, just some changed logic.
> >
> > I will post again later today with written details of the concurrency
> > rules we're working to now. I've left most of the isolation test
> > expected output as "TO BE DECIDED", so that we can agree our way
> > forwards.
>
> New patch attached that correctly handles all known concurrency cases,
> with expected test output.
>
> 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

Regards

Pavel

> --
> Simon Riggs http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>