On 26 January 2018 at 11:25, Simon Riggs <simon@2ndquadrant.com> wrote: > On 24 January 2018 at 04:12, Simon Riggs <simon@2ndquadrant.com> wrote: >> On 24 January 2018 at 01:35, Peter Geoghegan <pg@bowt.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