Re: [HACKERS] MERGE SQL Statement for PG11

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.

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