Re: [HACKERS] MERGE SQL Statement for PG11

On 27 October 2017 at 13:45, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> Simon Riggs wrote:
>
>> Earlier thoughts on how this could/could not be done were sometimes
>> imprecise or inaccurate, so I have gone through the command per
>> SQL:2011 spec and produced a definitive spec in the form of an SGML
>> ref page. This is what I intend to deliver for PG11.
>
> Nice work. I didn't verify the SQL spec, just read your HTML page;
> some very minor comments based on that:
>
> * use "and" not "where" as initial words in "when_clause" and
> "merge_update" clause definitions
>
> * missing word here: "the DELETE privilege on the if you specify"
>
> * I think the word "match." is leftover from some editing in the phrase
> " that specifies which rows in the data_source match rows in the
> target_table_name. match." In the same paragraph, it is not clear
> whether all columns must be matched or it can be a partial match.

Thanks for these review points, I have included them.

> * In the when_clause note, it is not clear whether you can have multiple
> WHEN MATCHED and WHEN NOT MATCHED clauses. Obviously you can have one
> of each, but I think your doc says it is possible to have more than one of
> each, with different conditions (WHEN MATCHED AND foo THEN bar WHEN
> MATCHED AND baz THEN qux). No example shows more than one.
>
> On the same point: Is there short-circuiting of such conditions, i.e.
> will the execution will stop looking for further WHEN matches if some
> rule matches, or will it rather check all rules and raise an error if
> more than one WHEN rules match each given row?

Docs rewritten to better explain.

> * Your last example uses ELSE but that appears nowhere in the synopsys.

This last has been removed.

New version of HTML docs attached for easy reading.

Attached: MERGE patch is now MOSTLY complete, but still WIP.

Patch works sufficiently well to take data from source and use it
correctly against target, for the DELETE operation and INSERT DEFAULT
VALUES. Patch also includes PL/pgSQL changes.

Patch has full set of docs and tests, but does not yet pass all tests.
UPDATE and INSERT are not yet working because I've chosen to leave
targetist handling until last, which is still WIP. The patch doesn't
crash, but does not yet work for those subcommands - though I haven't
prevented it from executing those subcommands.

Patch uses mechanism as agreed previously with Peter G et al. on this thread.

SUMMARY
Works
* EXPLAIN
* DELETE actions
* DO NOTHING actions
* PL/pgSQL
* Triggers for row and statement

Not yet working
* Execute UPDATE actions
* Execute INSERT actions
* EvalPlanQual
* No isolation tests yet
* RLS
* Partitioning

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